Order of Columns in the SET Clause in MySQL

Q

Is the Order of Columns in the SET Clause Important in MySQL?

✍: FYIcenter.com

A

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:

Oracle provides you the existing values from the database on columns names used in new value expressions.
MySQL provides you the updated values on columns names used in new value expressions. So the order of columns in the SET clause is important.

Tutorial exercise 1 - Setting "id" first and "counts" second:

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

mysql> UPDATE fyi_links SET id = id+200, counts = id*2 
   WHERE url = 'dev.fyicenter.com';
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 url = 'dev.fyicenter.com';
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 301 | dev.fyicenter.com | Good. |    602 | 2006-04-30    |
+-----+-------------------+-------+--------+---------------+
1 row in set (0.00 sec)

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

Notice that the "id" in the "counts" new value expression is taking the new value of the "id" column, because it appears before the "counts" columns in the SET clause.

Tutorial exercise 2 - Setting "counts" first and "id" second:

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

mysql> UPDATE fyi_links SET counts = id*2, id = id+200 
   WHERE url = 'dev.fyicenter.com';
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 url = 'dev.fyicenter.com';
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 301 | dev.fyicenter.com | Good. |    202 | 2006-04-30    |
+-----+-------------------+-------+--------+---------------+
1 row in set (0.00 sec)

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

Notice that the "id" in the "counts" new value expression is taking the old value of the "id" column, because it appears after the "counts" columns in the SET clause.

Â

â‡’INSERT, UPDATE and DELETE Statements in MySQL

â‡’â‡’MySQL Database Tutorials

2018-01-13, 1004👍, 0💬

UPDATE with Subquery Returning No Rows in MySQL
What Happens If the UPDATE Subquery Returns No Rows in MySQL? 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 SET clause. The tutorial exercise below shows you a good exampl... 2018-01-08, 1046👍, 0💬

Order of Columns in the SET Clause in MySQL
Is the Order of Columns in the SET Clause Important in MySQL? 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: Oracle provides you the existing values from the database on colu... 2018-01-13, 1005👍, 0💬

Use Existing Column Values in the SET Clause in MySQL
How To Use Existing Column Values in the SET Clause in MySQL? 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 exer... 2018-01-13, 973👍, 0💬

Violation of Unique Value Constraint in MySQL
What Happens If Unique Value Constraints Are Violated in MySQL? 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. The following tutorial exercise gives you some good examples:... 2018-01-16, 958👍, 0💬

Deleting Multiple Rows from a Table in MySQL
How To Delete Multiple Rows from a Table in MySQL? 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, coun... 2018-01-08, 952👍, 0💬

Deleting an Existing Row from a Table in MySQL
How To Delete an Existing Row from a Table in MySQL? 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);... 2018-01-08, 927👍, 0💬

Update Column Values on Multiple Rows in MySQL
How To Update Column Values on Multiple Rows in MySQL? 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_lin... 2018-01-13, 918👍, 0💬

Insert Multiple Rows with 1 INSERT Statement in MySQL
How To Insert Multiple Rows with One INSERT Statement in MySQL? 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 target table. The following tutorial exercise gives a good exa... 2018-01-16, 900👍, 0💬

UPDATE Using Data from Other Tables in MySQL
How To Use Values from Other Tables in UPDATE Statements in MySQL? 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 for each row in the update table that matches the WHERE clau... 2018-01-13, 893👍, 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.