MySQL Insert & Joins

问题:

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?

回答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

回答2:

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

回答3:

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