DELETE query for multiple records in MYSQL

user_17488585 0
Viewed 41

I am writing an MYSQL program in C. I want to delete the records from the database for all the id_no.'s stored in the array. I know that I can delete all the records by running the query in the loop. But the issue is that there are a lot of records I want to delete( > 300). So, I wanted to know if there are any other efficient methods I can use?

unit32_t id_nos[100];
char query[256];

for(int i=0; i< 100; i++) {
   sprintf(query, "DELETE FROM Students WHERE id = '%u'", id_nos[i]);

   if(mysql_query(con, query)) {
     printf("Failed to Delete record successfully\n")
     printf("stderr: %s\n", mysql_error(con));
     mysql_close(con);
     return;
   }
else {
 printf("Record deleted successfully\n");
}
c mysql
2 Answers

You can concatenate the ids as a string and passs in the "IN" function.

user_131886290

If your id are obtained by a previus query you con using a delete with join in this way you dan delete all the students row matching the id retured by the subquery just with a query

    DELETE Students 
    FROM Students
    INNER JOIN (
        select id from my_prev_table 
        where my_column = 'my_value'

    ) t on t.id = Students.id
user_35223120
Related