CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

update multiple rows with joins

I have this query in postgresql:

select *
from A s
join B m on (s.id=m.id)
where m.key=4 and s.ran=some_input_from_user

This gives me all the rows that I need to update.

I want to set A.value to be 90 for all these rows.

It doesn't look like a standart update query

if I do...

Update A set value=90 where.....

then I can't do the join.

any ideas how to do it?

Upvotes: 1

Views: 1025

Answers (3)

Hambone
Hambone

Reputation: 16377

This is the basic update syntax for PostgreSQL where you are updating based on a join to another table:

update A s
set
  value = 90
from B m
where
  s.id = m.id and
  m.key = 4 and
  s.ran = some_input_from_user

The trick is you never use the alias in the lvalue for the set commands. In other words, value = 90 is not s.value = 90. It seems minor, but I'm pretty sure it will prevent your query from working. The rationale is if you are updating table A (alias s) then any fields you are updating are, de-facto, from table A -- no need to alias them, and to allow aliases would almost imply you could update something other than A with this statement, which you cannot.

You can definitely use them in the rvalues, so this would certainly be okay (if it were your desire to update A based on B):

update A s
set
  value = m.salary * s.commission
from B m
where
  s.id = m.id and
 (s.value is null or
  s.value != m.salary * s.commission)

Upvotes: 2

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

Here is the query:

update a set value = 90 
where exists (
         select 1 from b 
         where a.id = b.id and b.key=4 
         and a.ran=some_input_from_user);

The above query will eliminate the requirement of reading table a twice.

Also you can use this query:

update a set value = 90 
where a.id in 
  (select b.id from b 
   where a.id = b.id and b.key = 4 
   and a.ran=some_input_from_user);

Upvotes: 1

Abdul Hannan Ijaz
Abdul Hannan Ijaz

Reputation: 844

TRY THIS

UPDATE A
SET A.VALUE = 90
from A
join B m on (A.id=m.id)
where m.key=4 and s.ran=some_input_from_user

Upvotes: -1

Related Questions