Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements

By: FYIcenter.com

Part:   1  2  3  4  5  6   7 

(Continued from previous part...)

What Happens If the UPDATE Subquery Returns Multiple Rows?

If a subquery is used in a UPDATE statement, it must return exactly one row for each row in the update table that matches the WHERE clause. If it returns multiple rows, MySQL server will give you an error message. To test this out, you can try the following tutorial exercise:

mysql> INSERT INTO fyi_rates VALUES (0, 'Number 1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fyi_rates VALUES (0, 'Number 2');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM fyi_rates WHERE id = 0;
+------+----------+
| id   | comment  |
+------+----------+
|    0 | Number 1 |
|    0 | Number 2 |
+------+----------+
2 rows in set (0.00 sec)

mysql> UPDATE fyi_links SET notes = (
    SELECT comment FROM fyi_rates 
    WHERE fyi_rates.id = fyi_links.id
   ) 
   WHERE id = 0;
ERROR 1242 (21000): Subquery returns more than 1 row

How To Delete an Existing Row from a Table?

If you want to delete an existing row from a table, you can use the DELETE statement with a WHERE clause to identify that row. Here is good sample of DELETE statements:

mysql> INSERT INTO fyi_links (url, id) 
   VALUES ('www.myspace.com', 301);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id, url, notes, counts FROM fyi_links 
   WHERE id = 301;
+-----+-----------------+-------+--------+
| id  | url             | notes | counts |
+-----+-----------------+-------+--------+
| 301 | www.myspace.com | NULL  |   NULL |
+-----+-----------------+-------+--------+
1 row in set (0.00 sec)

mysql> DELETE FROM fyi_links WHERE id = 301;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id, url, notes, counts FROM fyi_links 
   WHERE id = 301;
Empty set (0.00 sec)

How To Delete Multiple Rows from a Table?

You can delete multiple rows from a table in the same way as deleting a single row, except that the WHERE clause will match multiple rows. The tutorial exercise below deletes 3 rows from the fyi_links table:

mysql> SELECT id, url, notes, counts FROM fyi_links 
   WHERE id > 300;
+-----+-------------------+-------+--------+
| id  | url               | notes | counts |
+-----+-------------------+-------+--------+
| 801 | moc.retneciyf.ved | Wrong |   1602 |
| 802 | moc.retneciyf.abd | Wrong |   1604 |
| 803 | moc.retneciyf.aqs | Wrong |   1606 |
| 810 |                   | Wrong |   1620 |
| 700 | moc.retneciyf.www | Wrong |   1400 |
+-----+-------------------+-------+--------+
5 rows in set (0.00 sec)

mysql> DELETE FROM fyi_links WHERE id > 300;
Query OK, 5 rows affected (0.00 sec)

mysql> SELECT id, url, notes, counts FROM fyi_links 
   WHERE id > 300;
Empty set (0.00 sec)

(Continued on next part...)

Part:   1  2  3  4  5  6   7