Skip navigation links

MySQL Forums :: Certification :: error in question 12 of chapter 12: Joins practice question from MySQL 5 Study Guide


Advanced Search

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


Subject Written By Posted
error in question 12 of chapter 12: Joins practice question from MySQL 5 Study Guide Barrett Smith 06/04/2007 01:33PM
JOIN works for UPDATE/DELETE Luchezar Balev 06/04/2007 11:26PM
Re: JOIN works for UPDATE/DELETE Barrett Smith 06/05/2007 04:56AM
Re: JOIN works for UPDATE/DELETE Jacky Shu 06/05/2007 07:00AM
Re: JOIN works for UPDATE/DELETE Barrett Smith 06/05/2007 09:20AM
Re: error in question 12 of chapter 12: Joins practice question from MySQL 5 Study Guide Jacky Shu 06/05/2007 09:35AM


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.