Result consisted of more than one row

Question

0.00/5 (No votes)

See more:

My procedure

Java

CREATE procedure usp_set_night_free(booking_id bigint)
BEGIN

    SELECT b.price INTO @price_per_night FROM booked_rooms as b WHERE b.booking_id = booking_id LIMIT 1;

    UPDATE bookings as b
        JOIN booked_rooms b2 on b2.id = b.id
    SET b.total_price = b.total_price - @price_per_night
    WHERE b.id = b2.booking_id;
END;

Returns

Result consisted of more than one row

I am not really sure where the mistake could be, when I run the queries in the console separately I get -

1 row affected

for both queries.

What I have tried:

I tried with LIMIT 1 but the problem is elsewhere.

Posted 22-Feb-22 7:50am

Member 15471521

Updated 22-Feb-22 21:14pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2022-02-22T21:14:00

Your update query seems wrong. As per your schema design. 1 booking may have multiple booked rooms which is 1 to many relationships.

Below query will update the same booking multiple times because of 1 to many relationships. On top of that, your update is updating all bookings because you have not applied any filter criteria.

SQL

UPDATE bookings as b
JOIN booked_rooms b2 on b2.id = b.id
SET b.total_price = b.total_price - @price_per_night
WHERE b.id = b2.booking_id;

You need to change the above query to this

SQL

UPDATE bookings
SET total_price = total_price - @price_per_night
WHERE id = booking_id //booking_id input parameter