CODEWITHSUNDEEP
postgresql
John
John

Reputation: 317

Error in update query

I have two entities STOCK(name,amount) and PC_SYSTEMS(name,c_name), and i wish to update the components needed to create the PC_system "Alienware", therefore i want it to subtract 1 from the amount of the components, needed to create the alienware system, in Stock.

Here's my query:

"UPDATE stock SET amount=amount-1 WHERE name = ( SELECT p.c_name FROM pc_systems p WHERE p.name='Alienware');"

I get a weird error that says:

" more than one row returned by a subquery used as an expression"

Would be happy if someone could help.

Edit:

I SOLVED this by putting an "IN" in my query instead of "=". Final code:

UPDATE stock SET amount=amount-1 WHERE name IN ( SELECT p.c_name FROM pc_systems p        WHERE p.name='Alienware');

Upvotes: 1

Views: 108

Answers (2)

Taryn
Taryn

Reputation: 247680

Since your subquery is returning more than one record you cannot use that syntax. try something like this:

UPDATE S
SET S.amount=S.amount-1
FROM Stock S
INNER JOIN pc_systems p
    ON S.name = p.c_name
WHERE p.name='Alienware'

or try this:

update stock 
set s.amount=s.amount-1
from stock s
inner join pc_systems p on S.name = p.c_name
where p.name='Alienware'

Upvotes: 1

Brett Wait
Brett Wait

Reputation: 397

What it means is this SQL returns more than 1 row. Make it so it returns exactly 1 row only.

SELECT p.c_name FROM pc_systems p WHERE p.name='Alienware'

Upvotes: 3

Related Questions