How To Use Existing Values in UPDATE Statements

Q

How To Use Existing Values in UPDATE Statements? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements

✍: FYIcenter.com

A

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.

2007-05-11, 5128👍, 0💬

What Happens If the UPDATE Subquery Returns No Rows
What Happens If the UPDATE Subquery Returns No Rows? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements If you use a subquery to assign new values in the SET clause in an UPDATE statement, and the subquery returns no rows for an outer row, MySQL will provide a NULL value to the SE... 2007-05-11, 5290👍, 0💬

What Happens If Unique Value Constraints Are Violated
What Happens If Unique Value Constraints Are Violated? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements If you are inserting a new record that has values violating a unique constraint, you will get an error. Note that primary key column has a unique value constraint by default. ... 2007-05-11, 5286👍, 0💬

What Happens If the UPDATE Subquery Returns Multiple Rows
What Happens If the UPDATE Subquery Returns Multiple Rows? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements 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, My... 2007-05-11, 5230👍, 0💬

How To Use Existing Values in UPDATE Statements
How To Use Existing Values in UPDATE Statements? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE 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 represen... 2007-05-11, 5129👍, 0💬

Is the Order of Columns in the SET Clause Important
Is the Order of Columns in the SET Clause Important? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements Yes. The order of columns in the SET clause of the UPDATE statement is important. There is a BIG DIFFERENCE between MySQL and Oracle on update columns with previous values: Orac... 2007-05-11, 5089👍, 0💬

How To Update Values on Multiple Rows
How To Update Values on Multiple Rows? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements 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... 2007-05-11, 5038👍, 0💬

How To Use Values from Other Tables in UPDATE Statements
How To Use Values from Other Tables in UPDATE Statements? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements If you want to update values in one table with values from another table, you can use a subquery as an expression in the SET clause. The subquery should return only one row... 2007-05-11, 5030👍, 0💬

How To Update Values in a Table
How To Update Values in a Table? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements 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,... 2007-05-11, 5017👍, 0💬

How To Insert Multiple Rows with One INSERT Statement
How To Insert Multiple Rows with One INSERT Statement? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the t... 2007-05-11, 4952👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.