CODEWITHSUNDEEP
postgresqlsql-update
Casey B.
Casey B.

Reputation: 279

Update Multiple Rows with Specific Values and Others With Zero

How do I efficiently update multiple rows with particular values for a_id 84, and then all other rows set to 0 for that same a_id?

products

p_id    a_id     best 
 111      81       99
 222      81       99
 666      82       99
 222      83       99  
 111      84       99       
 222      84       99
 333      84       99
 111      85       99  
 222      85       99

Right now I'm doing this:

SQL Fiddle

update products as u set
  best = u2.best
from (values
  (111, 84, 1),
  (222, 84, 2)
) as u2(p_id, a_id, best)
where u2.p_id = u.p_id AND u2.a_id = u.a_id
RETURNING u2.p_id, u2.a_id, u2.best

But this only updates the rows within values as expected. How do I also update rows not in values to be 0 with a_id = 84?

Meaning the p_id of 333 should have best = 0. I could explicitly include every single p_id but the table is huge.

Upvotes: 0

Views: 241

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

Assuming (p_id, a_id) is the PK - or at least UNIQUE and NOT NULL, this is one way:

UPDATE products AS u
SET    best = COALESCE(u2.best, 0)
FROM   products AS p
LEFT   JOIN ( VALUES
   (111, 84, 1),
   (222, 84, 2)
   ) AS u2(p_id, a_id, best) USING (p_id, a_id)
WHERE  u.a_id = 84
AND    u.a_id = p.p_id
AND    u.p_id = p.p_id 
RETURNING u2.p_id, u2.a_id, u2.best;

The difficulty is that the FROM list of an UPDATE is the equivalent of an INNER JOIN, while you need an OUTER JOIN. This workaround adds the table products to the FROM list (which is normally redundant), to act as left table for the LEFT OUTER JOIN. Then the INNER JOIN from products to products works.

To restrict to a_id = 84 additionally, add another WHERE clause saying so. That makes a_id = 84 redundant in the VALUES expression, but keep it there to avoid multiple joins that would only be filtered later. Cheaper.

If you don't have a PK or any other (combination of) UNIQUE NOT NULL columns, you can fall back to the system column ctid for joining products rows. Example:

Upvotes: 1

forpas
forpas

Reputation: 164054

Remove the condition u2.a_id = u.a_id from the ON clause and put it in the assignment with a CASE statement:

update products as u set
  best = case when u2.a_id = u.a_id then u2.best else 0 end
from (values
  (111, 84, 1),
  (222, 84, 2)
) as u2(p_id, a_id, best)
where u2.p_id = u.p_id

Upvotes: 1

Related Questions