CODEWITHSUNDEEP
mysql
F.A
F.A

Reputation: 29

Error 1172 : Result consisted more than one row (in SQL)

I have declared the variables (total,order_num,order_stts,id_cust,reMarks). But it have error that said result consisted of more than one row. How can i fix this error? Please help me fix this error, i'm new to sql.

From this code, there's several task to do:

 (1) update tble customer by setting the address to '90 TYT' if c_id= 1 
 (2) view order_no,status,c_id,item_total remarks. 
 (3) if item_total 0, then update table order_status by setting remarks = 'UNAVAILABLE',
         else select order_no,status,item_total,remarks where status = 'waiting'.


#drop procedure if exists usp_GetAnything;
delimiter //
create procedure usp_GetAnything()
begin

declare total int default 0;
declare order_num varchar(45) default 000;
declare order_stts varchar(45) default 000;
declare id_cust int default 0;
declare reMarks varchar (45) default 000;

select c_id,lname,address,city
from customer;
update customer
set address = '90 TYT'
where c_id = 1;

select o.order_no,o.o_status,c.c_id,o.item_total,o.remarks 
into  order_num,order_stts,id_cust,total,reMarks
from customer c, order_status o
where c.c_id=o.c_id;

    if (total > 0) then 
    update order_status o
    set reMarks = 'UNAVAILABLE'
    where order_num > '123';
    else  
    select order_num,order_stts,total,reMarks
    from order_status
    where order_stts = 'waiting';

end if;

end

Upvotes: 0

Views: 23412

Answers (2)

spencer7593
spencer7593

Reputation: 108430

The error is due to the INTO clause in the second SELECT statement. That wouldn't be an error if the SELECT returned no more than one row.

But if that SELECT returns more than one row, MySQL will throw 1172 error. (You can test this by adding a "LIMIT 1" clause on that "SELECT ... INTO" statement, and verifying that the error is not thrown.

The procedure runs a SELECT to return a resultset, then issues an UPDATE statement to modify zero or more rows in a table.

The next part of the procedure is confusing; it's not clear what we are trying to achieve. The SELECT can return zero, one or more rows. If the intent is to perform a conditional test to determine whether there are any rows with item_total > 0,

Then one possible "fix" (to avoid the 1172 error) would be to add 

AND o.item_total > 0 LIMIT 1

to the "SELECT ... INTO" statement.

Without an "ORDER BY", it's indeterminate which row would be returned. But it doesn't really look like it matters. Apart from the comparison of the returned o.item_total value, it doesn't look like the procedure is doing anything else with the columns returned.

The following UPDATE statement is only going to be executed if an item_total>0 row was found.

And the UPDATE statement will update all rows that have an order_number greater than a specified constant.

There doesn't appear to be any relationship to the row(s) returned by the previous SELECT ... INTO statement.

If the intent is to update the rows in order_status (that are related to a customer) if "any" row returned by the previous query has an item_total > 0, then the proposed "fix" would accomplish that.

The procedure then (conditionally) returns a second resultset (all rows from order_status with `order_stts = 'waiting'), but only if there wasn't any order_status row with and item_total>0.

We can fix the 1172 error, but this procedure has much bigger problems than that error. The whole procedure seems like bizarre approach to solving whatever problem it's supposed to be solving. It just looks like a jumble of SQL statements kludged together without a clear design.

Upvotes: 3

Akhil
Akhil

Reputation: 2602

This means 

select o.order_no,o.o_status,c.c_id,o.item_total,o.remarks 
from customer c, order_status o
where c.c_id=o.c_id;

the query above return multiple rows which is not acceptible if you are trying to store the result in a variable. So if you dont expect multiple rows, check your data, or if you are OK with the first row values, add limit 1 in the query 

select o.order_no,o.o_status,c.c_id,o.item_total,o.remarks 
into  order_num,order_stts,id_cust,total,reMarks
from customer c, order_status o
where c.c_id=o.c_id limit 1;

You may try searching for Result consisted more than one row in Stackoverflow and you should be getting plenty of answers!!!

Upvotes: 0

Related Questions