Is it possible to merge two records by using "delete from" statement?
mysql, sql
Solution
You could use `UPDATE IGNORE` with your update statement - this would skip any updates that caused duplicates. You would then follow this with a `DELETE` to clear the rows that had been skipped. For example:
UPDATE IGNORE mytable SET network = 'B' WHERE network = 'A';
DELETE FROM mytable WHERE network = 'A';
From the documentation:
With the `IGNORE` keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are updated to values that would cause data conversion errors are updated to the closest valid values instead.
Problem
I have some networked equipment that's attached to multiple networks/VLans (A, B & C), and other equipment that's just connected to one of the networks. When I remove or replace a network, I need to update my database to reflect what the equipment is attached to so I'm trying to write a mysql statement to do that, but I'm running into various road blocks. My table only has two fields and there cannot be duplicate records. My data example is ``` deviceID network 1 A 1 B 1 C 2 B 2 C 3 A 4 A 5 B ``` How can I merge network A into network B so the above table would look like... ``` deviceID network 1 B 1 C 2 B 2 C 3 B 4 B 5 B ``` My initial attempt was to just `set network = 'B' where network = 'A'`, followed by a `DELETE network 'A'` statement but that would create duplicates, which isn't allowed for that table - even though the duplicates would be brief. Using alternate methods, I just keep running into failed mysql statements by using `WHERE EXISTS` and various `FROM (SELECT)` statements. Is it possible to do in a single mysql statement? Do I need two? Any help is appreciated.