Collections:
Error: Subquery Returns More than 1 Row in MySQL
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL?
✍: FYIcenter.com
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, MySQL server will give you an error message. To test this out, you can try the following tutorial exercise:
mysql> INSERT INTO fyi_rates VALUES (0, 'Number 1'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO fyi_rates VALUES (0, 'Number 2'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM fyi_rates WHERE id = 0; +------+----------+ | id | comment | +------+----------+ | 0 | Number 1 | | 0 | Number 2 | +------+----------+ 2 rows in 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; ERROR 1242 (21000): Subquery returns more than 1 row
Â
⇒INSERT, UPDATE and DELETE Statements in MySQL
⇒⇒MySQL Database Tutorials
2018-01-08, 3812👍, 0💬
Popular Posts:
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...
What Is a Database Schema in Oracle? A schema is a collection of logical structures of data, or sche...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPA...