Updating one table records using another table values

Question

I have two tables, one contains author names and their contact addresses and the other includes author names and their IDs.

The author name in both tables has the same value and I want to get the ID from one table and update the other one where the names are the same.

I tried this query that I believe it works in T-SQL (the sub-query also works in MySQL) but it does not work in MySQL:

UPDATE authors
set ID = (select b.ID from authors a, authorsID b 
where trim(a.name)=trim(b.name));

Could you please advise me how to make this work? Thanks.

Please see this post, it may helps you - http://stackoverflow.com/a/1262848/1315357 — jnemecz, Mar 06 '13 at 19:40

score 3 · Accepted Answer · answered Mar 06 '13 at 19:39

3

UPDATE authors a
inner join authorsID b on trim(a.name) = trim(b.name)
set a.ID = b.ID

answered Mar 06 '13 at 19:39

juergen d

score 0 · Answer 2 · answered Mar 06 '13 at 19:43

0

Query:

UPDATE authors
SET ID = (SELECT b.ID 
          FROM authorsID b
          WHERE trim(authors.name)=trim(b.name))

answered Mar 06 '13 at 19:43

Justin

2 Answers2