MySQL Insert & Joins

Question

i saw this

In MySQL, joins work for INSERT, UPDATE, and DELETE statements. It's possible to change data in more than one table when joining tables in an UPDATE or DELETE statement.

in an answer for a question from the mysql certification guide. is it true? inserts with joins? an example of it?

Answer 1

You can INSERT ... SELECT with mysql, which is probably what they mean. For example:

INSERT INTO tableNew (col1, col2)
  SELECT tbl1.col1, tbl2.col2
  FROM tbl1 JOIN tbl2

Answer 2

You can do this for Update statement like this,

Update C
Join tableB B on B.id=C.bid
Join tableA A on A.id=B.aid
Set C.status='Active',
A.status='Active'
Where A.id=1 or A.id=2 or A.id=3

check this for your reference.

http://www.siusic.com/wphchen/mysql-update-statement-159.html

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

Answer 3

To complete the set, here's one for DELETE. This is a common method for deleting rows together with their dependencies without triggers.

DELETE users, comments
FROM users JOIN comments ON comments.author=users.id
WHERE users.isspammer=1