CODEWITHSUNDEEP
sqldatabasepostgresql
Anil
Anil

Reputation: 141

Unable to use Except in update postgres

I am facing a problem when I am trying to use Except with an update statement, It is required because I don't want to use <> for each field when updating a record.

Here is the real example where I am trying to use except 

    update_existing_item_sales AS(
    UPDATE daily_item_sales 
    SET sales_amt = jd.sales_amt,
    sales_qty = jd.sales_qty, 
    sales_prc=jd.sales_prc,
    sales_promo_amt = jd.sales_promo_amt, 
    sales_promo_qty = jd.sales_promo_qty, 
    discount_amt = jd.discount_amt, 
    discount_qty = jd.discount_qty, 
    coupon_amt = jd.coupon_amt, 
    coupon_qty = jd.coupon_qty, 
    price_override_amt = jd.price_override_amt, 
    price_override_qty = jd.price_override_qty, 
    return_amt = jd.return_amt, 
    return_qty = jd.return_qty, 
    item_scanned = jd.item_scanned, 
    item_keyed = jd.item_keyed 
    FROM updated_Item_data jd 
    WHERE daily_item_sales.id = jd.id
    EXCEPT (select jd.* from updated_Item_data jd 
        INNER JOIN daily_item_sales on 
    WHERE daily_item_sales.id = jd.id 
    AND daily_item_sales.sales_amt = jd.sales_amt 
    AND daily_item_sales.sales_qty = jd.sales_qty 
    AND daily_item_sales.sales_promo_amt = jd.sales_promo_amt
    AND daily_item_sales.sales_promo_qty = jd.sales_promo_qty
    AND daily_item_sales.discount_amt = jd.discount_amt
    AND daily_item_sales.discount_qty = jd.discount_qty
    AND daily_item_sales.coupon_amt = jd.coupon_amt
    AND daily_item_sales.coupon_qty = jd.coupon_qty
    AND daily_item_sales.price_override_amt = jd.price_override_amt
    AND daily_item_sales.price_override_qty = jd.price_override_qty
    AND daily_item_sales.return_amt = jd.return_amt
    AND daily_item_sales.return_qty = jd.return_qty
    AND daily_item_sales.item_scanned = jd.item_scanned
    AND daily_item_sales.item_keyed = jd.item_keyed)
)

The error:

ERROR:  syntax error at or near "EXCEPT"

                                            ^
SQL state: 42601
Character: 37

Upvotes: 0

Views: 164

Answers (3)

Abelisto
Abelisto

Reputation: 15624

Could be simplified as

update daily_item_sales as t set
    (sales_amt, sales_qty, sales_prc, ...) = (s.sales_amt, s.sales_qty, s.sales_prc, ...)
from updated_Item_data as s
where
    t.id = s.id and
    (t.sales_amt, t.sales_qty, t.sales_prc, ...) is distinct from (s.sales_amt, s.sales_qty, s.sales_prc, ...)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

EXCEPT is a set-based operator -- like UNION, UNION ALL, and INTERSECT -- that connects result sets generated by SELECT. It does not make sense with UPDATE.

But, it is easily replaced using NOT EXISTS:

with u as (
    UPDATE daily_item_sales 
        SET sales_amt = jd.sales_amt,
            sales_qty = jd.sales_qty, 
            sales_prc=jd.sales_prc,
            sales_promo_amt = jd.sales_promo_amt, 
            sales_promo_qty = jd.sales_promo_qty, 
            discount_amt = jd.discount_amt, 
            discount_qty = jd.discount_qty, 
            coupon_amt = jd.coupon_amt, 
            coupon_qty = jd.coupon_qty, 
            price_override_amt = jd.price_override_amt, 
            price_override_qty = jd.price_override_qty, 
            return_amt = jd.return_amt, 
            return_qty = jd.return_qty, 
            item_scanned = jd.item_scanned, 
            item_keyed = jd.item_keyed 
        FROM updated_Item_data jd 
        WHERE daily_item_sales.id = jd.id AND
              NOT EXISTS (SELECT 1
                          FROM daily_item_sales dis
                          WHERE dis.id = jd.id AND 
                                dis.sales_amt = jd.sales_amt AND  
                                dis.sales_qty = jd.sales_qty AND 
                                dis.sales_promo_amt = jd.sales_promo_amt AND 
                                dis.sales_promo_qty = jd.sales_promo_qty AND 
                                dis.discount_amt = jd.discount_amt AND 
                                dis.discount_qty = jd.discount_qty AND 
                                dis.coupon_amt = jd.coupon_amt AND 
                                dis.coupon_qty = jd.coupon_qty AND 
                                dis.price_override_amt = jd.price_override_amt AND
                                dis.price_override_qty = jd.price_override_qty AND
                                dis.return_amt = jd.return_amt AND
                                dis.return_qty = jd.return_qty AND
                                dis.item_scanned = jd.item_scanned AND
                                dis.item_keyed = jd.item_keyed
                               )
. . .

That said, this is probably better handled using ON CONFLICT with a unique constraint.

Upvotes: 1

Andronicus
Andronicus

Reputation: 26066

You can rewrite the query as follows:

update app_user 
set deleted = false 
where end_timestamp is not null

Upvotes: 1

Related Questions