Categories:
.NET (961)
C (387)
C++ (185)
CSS (84)
DBA (8)
General (31)
HTML (48)
Java (641)
JavaScript (220)
JSP (109)
JUnit (31)
MySQL (297)
Networking (10)
Oracle (562)
Perl (48)
Perl (9)
PHP (259)
PL/SQL (140)
RSS (51)
Software QA (28)
SQL Server (5)
Struts (20)
Unix (2)
Windows (3)
XHTML (199)
XML (59)
Other Resources:
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
✍: FYIcenter.com
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:
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.
2007-05-11, 4650👍, 0💬
Popular Posts:
Write down the equivalent pointer expression for referring the same element a[i][j][k][l]? a[i] == *...
What is the difference between const char* p and char const* p? In const char* p, the character poin...
How did you implement UML in your project ? PART II Implementation phase / Coding phase (Class diagr...
What Happens If a Hyper Link Points to a Music File? - XHTML 1.0 Tutorials - Understanding Hyper Lin...
The following variable is available in file1.c, who can access it? static int average; Answer: all t...