How to solve mysql error Subquery returns more than 1 row

mysql error: Subquery returns more than 1 row

mysql error: SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

The error means that the subquery result is more than one row.

The error is reported as follows

Solution

Take this sql statement as an example

select * from table1 where table1.colums=(select columns from table2);

1) If it is written Repeat, remove duplicate data. When writing data, you can use logical judgment (PHP) or foreign keys (MySQL) to prevent repeated writing of data.

(What I encountered in actual development was the situation of repeated data writing. I found two identical pieces of data in the database, which did not meet the original business requirements)

2) Add limit 1 to the subquery conditional statement and find one that meets the conditions

select * from table1 where table1.colums=(select columns from table2 limit 1);

3) Add the any keyword before the subquery

select * from table1 where table1.colums=any(select columns from table2);

Error code: 1242 Subquery returns more than 1 row

Error description

1 queries executed, 0 success, 1 errors, 0 warnings

Query: SELECT t.id, DATE_FORMAT( t.statisTime, ' %Y-%m-%d %H:%i:%s' ) statusTime, (SELECT `id` FROM t_truck_info WHERE id = t.plateId...

Error code: 1242Subquery returns more than 1 row

Execution time: 0.009 sec Transmission time: 0.002 sec Total time: 0.012 sec

Error reason

When writing the query SQL statement, there are A field is obtained from another table

select t.id,(select num from t_user_info where id = stuNo) as amount from t_stu_info t left join t_user_info t0
on t0.id = t.stuNo

The query shows that num is multiple pieces of data, and the outer query result requires num to be one piece of data

Solution

select t.id,(select sum(num) from t_user_info where id = stuNo) as amount from t_stu_info t left join t_user_info t0
on t0.id = t.stuNo

The above is the detailed content of How to solve mysql error Subquery returns more than 1 row. For more information, please follow other related articles on the PHP Chinese website!