Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
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
✍: FYIcenter.com
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 example:
mysql> UPDATE fyi_links SET notes = 'Number one' WHERE id = 0; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT id, url, notes, counts FROM fyi_links WHERE id = 0; +----+-------------------+------------+--------+ | id | url | notes | counts | +----+-------------------+------------+--------+ | 0 | www.fyicenter.com | Number one | NULL | +----+-------------------+------------+--------+ 1 row in set (0.00 sec) mysql> SELECT * FROM fyi_rates WHERE id = 0; Empty 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; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT id, url, notes, counts FROM fyi_links WHERE id = 0; +----+-------------------+-------+--------+ | id | url | notes | counts | +----+-------------------+-------+--------+ | 0 | www.fyicenter.com | NULL | NULL | +----+-------------------+-------+--------+ 1 row in set (0.00 sec)
2007-05-11, 5094👍, 0💬
Popular Posts:
What is the method to customize columns in DataGrid? Use the template column.
How To Check the Oracle TNS Settings? - Oracle DBA FAQ - ODBC Drivers, DSN Configuration and ASP Con...
Why is there extra white space before or after tables? This is often caused by invalid HTML syntax. ...
What's difference between HashTable and ArrayList ? You can access array using INDEX value of array,...
What’s the difference between Unit testing, Assembly testing and Regression testing? Unit testing is...