Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.0 (Debian 12.0-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


CREATE TABLE inventory
(
  item_id INTEGER NOT NULL,
  location_id VARCHAR (25) NOT NULL
);


INSERT INTO inventory VALUES (123453, '12-099'), (123454, '12-100'), (123456, '12-101'),
(123457, '12-102'), (123458, '12-103');
5 rows affected

CREATE TABLE temp_inv
(
  item_id INTEGER NOT NULL,
  location_id VARCHAR (25) NOT NULL
);

INSERT INTO temp_inv VALUES (123456, '13-101'), (123457, '13-102'), (123458, '13-103');
3 rows affected

SELECT * FROM temp_inv;


item_id
location_id


123456
13-101

123457
13-102

123458
13-103


SELECT * FROM inventory;


item_id
location_id


123453
12-099

123454
12-100

123456
12-101

123457
12-102

123458
12-103


UPDATE inventory
SET location_id = temp_inv.location_id
FROM temp_inv
WHERE inventory.item_id = temp_inv.item_id;
3 rows affected

SELECT * FROM inventory;


item_id
location_id


123453
12-099

123454
12-100

123456
13-101

123457
13-102

123458
13-103

item_id	location_id
123453	12-099
123454	12-100
123456	12-101
123457	12-102
123458	12-103