Reputation: 17
How do i update inventory_id in TABLE2 from TABLE1 inventory_id?
So far I've tried
UPDATE TABLE2
SET
inventory_id=t1.inventory_id
FROM TABLE2 t2
INNER JOIN
TABLE1 t1
ON t1.test_id = t2.test_id
WHERE t1.test_id = t2.test_id;
But this sets all the value of inventory_id in TABLE2 as 1 instead of my expected result.
TABLE1
inventory_id test_id
1 26
2 56
3 12
4 67
TABLE2
test_id inventory_id
12
26
56
67
Such that it becomes like this?
TABLE2
test_id inventory_id
12 3
26 1
56 2
67 4
Upvotes: 0
Views: 3907
Reputation: 149185
Normally the PostgreSQL UPDATE JOIN syntax should give:
UPDATE TABLE2 t2
SET
t2.inventory_id=t1.inventory_id
FROM TABLE1 t1
WHERE t1.test_id = t2.test_id;
Upvotes: 0
Reputation: 37527
The documentation on UPDATE
states:
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] ... [ FROM from_item [, ...] ]
(...)
from_item
A table expression allowing columns from other tables to appear in the
WHERE
condition and update expressions. This uses the same syntax as theFROM
clause of aSELECT
statement; for example, an alias for the table name can be specified. Do not repeat the target table as afrom_item
unless you intend a self-join (in which case it must appear with an alias in thefrom_item
).
(emphasis: me)
So following that you want:
UPDATE table2 t2
SET inventory_id = t1.inventory_id
FROM table1 t1
WHERE t1.test_id = t2.test_id;
You pretty much had it but seem to got confused by the syntax you may have seen for other DBMS and mixed them.
Upvotes: 3