CODEWITHSUNDEEP
sqlpostgresqlsql-update
krltos
krltos

Reputation: 335

updating more than one row at the time with select returning multiple ids

I want to update a table based on the following query in PostgreSQL

UPDATE tableX
SET columnX = 100
WHERE id = (SELECT ID FROM tableY WHERE col1 = 'Y' AND col2 = 'Z');

And the problem is:

Select query can return more than one row

. How do I update the table with only one query?

Upvotes: 1

Views: 126

Answers (3)

rpy
rpy

Reputation: 4013

And for completeness:

You need not use a subquery in the first place. You could also use:

update tableX
set columnX = 100
from tableY
where tableX.id=tableY.id
and tableY.col1 = 'Y'
and tableY.col2 = 'Z';

Upvotes: 2

cdaiga
cdaiga

Reputation: 4939

You can check that the id is in the list returned.

update tableX
set columnX = 100
where id in (select id from tableY where col1 = 'Y' and col2 = 'Z');

Upvotes: 2

DineshDB
DineshDB

Reputation: 6193

Use IN instead of =:

update tableX
set columnX = 100
where id IN (select id from tableY where col1 = 'Y' and col2 = 'Z');

Upvotes: 3

Related Questions