MySQL用查询匹配的concat更改id

Question

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 1

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!