Menu
I have a code with two mysql queries.DELETE FROM my_table WHERE user_id=some_numberINSERT INTO my_table (user_id, ... ) VALUES(some_number, ...)
The field user_id is unique.
In rare cases the insert fails claiming a duplicate entry occurred. My first instinct leads me to to believe the DELETE didn't finish and now the insert is trying to insert and I'm getting a duplicate entry. Is this possible? How can I avoid this? Might there be a different explanation you can think of?
Update: The reason I'm deleting is because I want to delete all the data that I am not updating / inserting for the first time. Also, I think it is important to state that most of the data remains the same.
827
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
Solution. By default, MySQL workbench is started in safe mode, and can't update or delete, without a “WHERE” condition, see the error message. To fix it, in menu, selects “Edit” -> “Preferences” -> “SQL Queries”, uncheck the “Safe Updates” checkbox. Done, try reconnect and issue the delete command again.
However, if you use the INSERT IGNORE statement, the rows with invalid data that cause the error are ignored and the rows with valid data are inserted into the table. The syntax of the INSERT IGNORE statement is as follows: INSERT IGNORE INTO table(column_list) VALUES( value_list), ( value_list), ...
Using DELETE to remove rows from tablesThe SQL DELETE command is used to remove rows from tables, functioning as the complementary action to INSERT . In order to remove rows from a table, you must identify the rows you wish to target by providing match criteria within a WHERE clause.
SET AUTOCOMMIT=0;
START TRANSACTION;
DELETE FROM my_table WHERE user_id=some_number;
INSERT INTO my_table (user_id, ... ) VALUES(some_number, ...);
commit;
Use an UPDATE statement instead:
UPDATE my_table
SET my_column = my_value
WHERE user_id = some_number
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
