CODEWITHSUNDEEP
sqlpostgresql
Mj Ebrahimzadeh
Mj Ebrahimzadeh

Reputation: 647

How can I update if value is different or empty

I want to update my column if the vlaue is different from last value or its empty. I came up with this sql but it gives this error:

missing FROM-clause entry for table "box_per_pallet"

SQL:

UPDATE products AS p
            SET box_per_pallet[0] = (CASE WHEN p.box_per_pallet.length = 0 THEN 0 ELSE p.box_per_pallet[0] END)
            WHERE sku = 'A' AND store_id = 1

Upvotes: 0

Views: 121

Answers (1)

J Spratt
J Spratt

Reputation: 2012

This is what I came up with based on your input. ARRAY_LENGTH takes the array and the dimension you want to check the length of as parameters. This missing from clause is because Postgres thinks that p.box_per_pallet is something other than an array and it can't find that anywhere in the query. You can't use the dot operator on arrays like p.box_per_pallet.length. It's like saying, "find the length field on table box_per_pallet in schema p".

UPDATE products
   SET box_per_pallet[0] = CASE WHEN ARRAY_LENGTH(box_per_pallet, 1) = 0
                                  OR box_per_pallet IS NULL
                                  OR box_per_pallet[0] <> 0 -- your new value?
                                THEN 0
                                ELSE box_per_pallet[0]
                                END
 WHERE sku = 'A'
   AND store_id = 1
;

Here is a link to a dbfiddle showing the idea.

Upvotes: 1

Related Questions