SQL-Basics – Subqueries: Update column with values from another column

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");


id device info
1 Device_1 Works!
2 Device_2 Doesn’t work!


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!