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

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!

Leave a Reply

Your email address will not be published. Required fields are marked *