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");
}
You can concatenate the ids as a string and passs in the "IN" function.
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