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