CODEWITHSUNDEEP
sqlpostgresql
lapots
lapots

Reputation: 13395

update table column using values from another table column

I've got two tables that look like this

TABLE_1
option_id PK,
condition_id FK,

And I has another table that looks like this

TABLE_2
option_id PK, FK -> TABLE_1
condition_id PK, FK

I want to set condition_id in TABLE_1 with corresponding values for condition_id from TABLE_2.

My script looks like this

UPDATE TABLE_1
SET 
    condition_id = t2.condition_id
FROM TABLE_1 t1
INNER JOIN TABLE_2 t2
ON t1.option_id = t2.option_id

But it seems to be wrong - after the execution all the values of condition_id in TABLE_1 are the same.

What is wrong?

Upvotes: 0

Views: 57

Answers (2)

rpy
rpy

Reputation: 4013

The problem is: you are using two instances of TABLE_1.

UPDATE TABLE_1   <-- first instance

FROM TABLE_1 t1 <-- second instance

Thus, while the FROM allows you to refer to a combined structure that relates matching entries, this forms a full cross join with the instance of TABLE_1 that is being updated. To avoid this you would need to add a further condition like WHERE TU.option_id=t1.option_id. (I introduced TU as an alias for the update target table to avoid ambiguity.)

Or, likely, you might simply use:

UPDATE TABLE_1 t1
SET 
    condition_id = t2.condition_id
FROM TABLE_2 t2
WHEREt1.option_id = t2.option_id

Upvotes: 1

L&#233;o R.
L&#233;o R.

Reputation: 2698

Something like this should do it :

UPDATE table1 
   SET table1.condition_id= table2.condition_id
   FROM table1  INNER JOIN  table2 ON table1.option_id = table2.option_id

Upvotes: 0

Related Questions