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...)

How To Update Values in a Table?

If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The tutorial script below shows a good example:

mysql> UPDATE fyi_links SET counts = 999, notes = 'Good.' 
   WHERE id = 101;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT id, url, notes, counts, DATE(created) 
   FROM fyi_links WHERE id = 101;
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 101 | dev.fyicenter.com | Good. |    999 | 2006-04-30    |
+-----+-------------------+-------+--------+---------------+
1 row in set (0.00 sec)

How To Update Values on Multiple Rows?

If the WHERE clause in an UPDATE matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example:

mysql> UPDATE fyi_links SET counts = 9, notes = 'Wrong' 
   WHERE id >= 500;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT id, url, notes, counts, DATE(created) 
   FROM fyi_links WHERE id >= 500;
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 601 | moc.retneciyf.ved | Wrong |      9 | 2006-04-30    |
| 602 | moc.retneciyf.abd | Wrong |      9 | 2006-08-31    |
| 603 | moc.retneciyf.aqs | Wrong |      9 | 2006-08-31    |
| 610 |                   | Wrong |      9 | 2006-08-31    |
| 500 | moc.retneciyf.www | Wrong |      9 | 2006-08-31    |
+-----+-------------------+-------+--------+---------------+
5 rows in set (0.00 sec)

This statement updated 5 rows with the same new values on all 5 rows.

How To Use Existing Values in UPDATE Statements?

If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by column names in the expressions. The tutorial exercise below shows a good example:

mysql> UPDATE fyi_links SET id = id+200, counts = id*2 
   WHERE id >= 500;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

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

This statement increased values in the id column by 200. It also updated the counts column with the newly increased id value.

(Continued on next part...)

Part:   1  2  3   4  5  6  7