A. Lau A. Lau - 1 month ago 6
MySQL Question

MySQL changing id with concat where query matches

So I have three tables. I want the first table to match the third table based on their matching ids. Afterwards, I want the third table's id to be replaced by the second table's id (concat'd with a 'g' in front), which is identified through a

select
query on the first and second table.

Here's my rough idea of how it should go, just not too sure of how to match, then use tbl2's id:

UPDATE tbl3 SET col=CONCAT('g',tbl2.id) WHERE
id IN (
SELECT tbl1.id, tbl2.id FROM tbl1 left join tbl2 on tbl1.id = tbl2.id
WHERE blah='blah');


Is this possible in MySQL alone or will I have to do it in php using multiple queries?

Answer

So I found a MySQL fiddle site. Not bad, but doesn't allow UPDATE. Anyway, at least you can see the SELECT working:

http://sqlfiddle.com/#!9/bfdb66/7

Here's the SQL:

UPDATE tbl3 t3
LEFT JOIN tbl1 t1 ON t3.id=t1.id 
LEFT JOIN tbl2 t2 ON t2.id=t1.id
SET t3.col=CONCAT('g',t2.id) WHERE
t3.blah = 'blah' AND t2.id IS NOT NULL;

According to this spec:

  • I want the first table to match the third table based on their matching ids
  • I want the third table's id to be replaced by the second table's id
  • which is identified through a select query on the first and second table

Let me know if I've misunderstood!