update table column using values from another table column

Question

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?

Which dbms are you using?

mshsayem
– mshsayem

2018-01-25 08:58:34 +00:00
Commented Jan 25, 2018 at 8:58 — mshsayem
– mshsayem, Commented Jan 25, 2018 at 8:58
@mshsayem updated tags with DBMS

lapots
– lapots

2018-01-25 08:59:25 +00:00
Commented Jan 25, 2018 at 8:59 — lapots
– lapots, Commented Jan 25, 2018 at 8:59

rpy · Accepted Answer · 2018-01-25 09:23:04Z

1

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

answered Jan 25, 2018 at 9:23

rpy

4,0432 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Léo R. · Accepted Answer · 2018-01-25 09:00:30Z

0

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

answered Jan 25, 2018 at 9:00

Léo R.

2,7161 gold badge13 silver badges23 bronze badges

1 Comment

lapots Over a year ago

but isn't my script the same?

Collectives™ on Stack Overflow

update table column using values from another table column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related