How to update a postgres table with a null test?

Question

I need to update the name and description in Table 1,

the values from Table 2.

If there are no values in Table 2, then do not overwrite the name or description in Table 1 with null. I made 2 requests and ask them to help me optimize them.

Update requests:

UPDATE final
SET
  name        = product_info.name
FROM product_info
WHERE final.xml_id = product_info.xml_id
      AND product_info.name NOTNULL;

UPDATE final
SET
  description        = product_info.description
FROM product_info
WHERE final.xml_id = product_info.xml_id
      AND product_info.description NOTNULL;

And wha tis the issue with your queries?

Gordon Linoff
– Gordon Linoff

2017-06-23 11:28:16 +00:00
Commented Jun 23, 2017 at 11:28 — Gordon Linoff
– Gordon Linoff, Commented Jun 23, 2017 at 11:28

Gordon Linoff · Accepted Answer · 2017-06-23 11:29:58Z

1

You can do this in a single query:

UPDATE final f
    SET name = COALESCE(pi.name, f.name),
        description = COALESCE(pi.description, f.description)
FROM product_info pi
WHERE f.xml_id = pi.xml_id AND
      (pi.name IS NOT NULL OR pi.description IS NOT NULL);

answered Jun 23, 2017 at 11:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Miksser Over a year ago

It did not work. Overwritten value name =(( The "and" structure overwrites the 2 values in table 1. If any value is not null. gyazo.com/dd7a507a3f7cbfba582882438442a841

Gordon Linoff Over a year ago

@Miksser . . . I have no idea what your comment refers to. This will assign the product_info values to name and description whenever they are not NULL. That is the same logic as your queries. Perhaps, however, the "empty" or "NULL" values in your data are actually strings.

Miksser Over a year ago

There was everywhere null, but your script still earns after removing AND (Pi.name IS NOT NULL OR pi.description IS NOT NULL); Thanks ^^

Collectives™ on Stack Overflow

How to update a postgres table with a null test?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related