CODEWITHSUNDEEP
sqlpostgresql
Some Guy
Some Guy

Reputation: 13568

How to update rows in a table that are different from another table?

I have two tables

boxes
id, name, width, height, is_changed
1, ben, 10, 12, false
2, carol, 13, 10, false
3, david, 2, 33, false
4, ellen, 9, 17, false

boxes_new
id, name, width, height
1, ben, 1000, 12
2, xavier, 13, 10
3, david, 2, 33
4, ellen, 9, 17

Note that boxes 1 and 2 are different in boxes_new

I want to do something like

UPDATE boxes SET is_changed=true WHERE boxes.name != boxes_new.is_name OR boxes.width != boxes_new.width

Also, these tables will have hundreds of thousands of rows, if that matters for performance reasons.

Upvotes: 1

Views: 21

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656734

If NULL values are possible, to cover all possibilities you have to use NULL-safe comparison:

UPDATE boxes b
SET    is_changed = TRUE
FROM   boxes_new b1
WHERE  b.id = b1.id
AND   (b.name, b.width, b.height) IS DISTINCT FROM (b1.name, b1.width, b1.height);

Upvotes: 0

Bart
Bart

Reputation: 11

I think some nested query like this, could help,

UPDATE boxes SET is_changed=true WHERE id NOT IN (SELECT b.id FROM boxes b INNER JOIN boxes_new bn ON (bname = bn.name AND b.width = bn.width));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming that id matches two boxes:

update boxes b
    set is_changed = true
    from boxes_new bn
    where bn.id = b.id and
          (b.name <> bn.name or
           b.width <> bn.width or
           b.height <> bn.height
          );

Upvotes: 1

Related Questions