MySQL Forums
Forum List  »  Certification

JOIN works for UPDATE/DELETE
Posted by: Luchezar Balev
Date: June 04, 2007 11:26PM

Hello!

The most correct answer is 3, I think:
3. Joins work for SELECT, UPDATE, and DELETE statements.
Maybe they have misspelled INSERT instead of SELECT.

JOIN works with UPDATE and DELETE ;)

Example:

mysql> select * from names;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Lucho |
|    2 | Lily  |
|    3 | Kiro  |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from prof;
+-----------+------------+
| person_id | prof       |
+-----------+------------+
|         1 | Programmer |
|         2 | QA         |
|         3 | Sys admin  |
+-----------+------------+
3 rows in set (0.00 sec)

mysql> update names inner join prof on (id = person_id) set prof = 'Workless' where name = 'Kiro';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from prof;
+-----------+------------+
| person_id | prof       |
+-----------+------------+
|         1 | Programmer |
|         2 | QA         |
|         3 | Workless   |
+-----------+------------+
3 rows in set (0.00 sec)

mysql> delete from names, prof using names inner join prof on (id = person_id) where name = 'Kiro';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from names;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Lucho |
|    2 | Lily  |
+------+-------+
2 rows in set (0.00 sec)

mysql> select * from prof;
+-----------+------------+
| person_id | prof       |
+-----------+------------+
|         1 | Programmer |
|         2 | QA         |
+-----------+------------+
2 rows in set (0.00 sec)

mysql>

Regards!

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.