-- update
-- update where
update products set price = 10 where price = 5;

-- no where
UPDATE products SET price = price * 1.10;

-- set muliple
update mytable set a = 5, b = 3, c = 1 where a > 0;

-- sub_select
update t set (z) = (select 1);


-- with_stmt
with t1 as (
  select 1
)
update t2 set 
  foo = default
from t1 where t2.foo_id = t1.foo_id;

-- based_on_schema
-- all options
update only t as t2 
set 
  bar = a * 2,
  foo = default,
  (a) = row(default),
  (b, c) = (2, 3),
  (z) = (select 1)
from a, b
where t2 = t
returning *, foo * 2 as bar;

-- pg_docs
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03';

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp;

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco' AND date = '2003-07-03';

UPDATE employees SET sales_count = sales_count + 1 FROM accounts
  WHERE accounts.name = 'Acme Corporation'
  AND employees.id = accounts.sales_person;

UPDATE employees SET sales_count = sales_count + 1 WHERE id =
  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

UPDATE accounts SET (contact_first_name, contact_last_name) =
    (SELECT first_name, last_name FROM employees
     WHERE employees.id = accounts.sales_person);

UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM employees WHERE employees.id = accounts.sales_person;

UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
     WHERE d.group_id = s.group_id);

BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;

UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

WITH exceeded_max_retries AS (
  SELECT w.ctid FROM work_item AS w
    WHERE w.status = 'active' AND w.num_retries > 10
    ORDER BY w.retry_timestamp
    FOR UPDATE
    LIMIT 5000
)
UPDATE work_item SET status = 'failed'
  FROM exceeded_max_retries AS emr
  WHERE work_item.ctid = emr.ctid;


-- returning_with_old_new
UPDATE inventory
SET quantity = quantity - 5
WHERE product_id = 456
RETURNING WITH (OLD AS before, NEW AS after)
    before.quantity AS previous_quantity,
    after.quantity AS current_quantity,
    after.quantity - before.quantity AS change;

update t set x = 1 
returning with (old as o, new as n) o.*, n.*;

-- subscripts and field access in set targets
UPDATE t SET a[1] = 2, b[1:2] = '{1,2}', c.d = 3;
UPDATE t SET (a[1], b.c, d) = ROW(1, 2, 3);

-- for_portion_of
UPDATE t FOR PORTION OF valid_at FROM '2024-01-01' TO '2025-01-01' SET x = 1;

-- for_portion_of_range
UPDATE t FOR PORTION OF valid_at (period) SET x = 1;
