Lookup from another table to update a specific column

0
votes

I've been struggling with this for quite a while and can't seem to find the correct statement (even with the help of google!)

What I'm trying to do is... (and I really hope this makes sense)

Lookup value1 in table1 from value1 in table2 (to match) Then update value2 in table1 from table2 value2 where the first values match

so far I have.. 

inner join table2 on table1.value1
inner join table2 on table1.value2

update table1

set value2=table2.value2
where value1=table2.value1
2
sqlsql-serverjoin
Ignore the UPDATE bit for now. Can you write a SELECT statement that returns the desired result? – Strawberry
I hope the names you have on tables and columns are not the ones you show. – ypercubeᵀᴹ
Have a look at the instructions at decipherinfosys.wordpress.com/2007/01/31/…, maybe one of those approaches works with MySQL, too. – Janick Bernet

2 Answers

1
votes

You want a join with an update in mysql. The syntax is:

update table1 join
       table2
       on table1.value1 = table2.value1
    set table1.value2 = table2.value2;
1
votes

Sounds like this is what you are after:

update table1
  join table2
    on table1.value1 = table2.value1
set table1.value2 = table2.value2;

Demo: http://sqlfiddle.com/#!2/8982d/1

Update:

If you are using SQL Server (I see you have that tag in the question as well), then it's normal that you get the "syntax error newar JOIN" error message.

That's because in SQL Server the syntax is a bit different:

update table1
    set table1.value2 = table2.value2
from table1
join table2
  on table1.value1 = table2.value1

Demo: http://sqlfiddle.com/#!3/4139c/1