Sometimes You screw up your database design and you have redundancies i.e. your database is not normalized. If You want to correct that: Subqueries for the rescue!
In our example we have two tables which contain almost the same information:
CREATE TABLE installed_device (`id` int, `device` text, `info` text);
INSERT INTO installed_device (`id`, `device`, `info`) VALUES (1, "Device_1", "Works!"), (2, "Device_2", "Doesn't work!");
CREATE TABLE device (`id` int, `device_name` text);
INSERT INTO device (`id`, `device_name`) VALUES (1, "Device_1"), (2, "Device_2");
installed_device
| id | device | info |
|---|---|---|
| 1 | Device_1 | Works! |
| 2 | Device_2 | Doesn’t work! |
device
| id | device_name |
|---|---|
| 1 | Device_1 |
| 2 | Device_2 |
The goal is to replace the values in installed_device’s device column with the id from table device:
UPDATE installed_device SET device = (SELECT id FROM device WHERE device.device_name = installed_device.device);
The Result
| id | device | info |
|---|---|---|
| 1 | 1 | Works! |
| 2 | 2 | Doesn’t work! |
