Update Columns in the Same Table With Different Values With Postgres

Update columns in the same table with different values with postgres

There is a way to do this in a single statement using case:

update my_table
set name = case name 
    when 'My Name' then 'Updated My Name'
    else 'Updated My Name222'
    end
where name in ('My Name', 'My Name222');

In this case two separate updates are simpler and quite natural.

However, if you want to modify the column values exactly in the same way in both rows, then this solution makes more sense:

update my_table
set name = concat('Updated ', name)
where name in ('My Name', 'My Name222');

PostgreSQL update column from another column in same table

update labor set type_ = left(code::text, 1)::int8

update multiple column in table using same table data in postgres?

Your bracketsfull expression can be written down in much simpler form. Use it twice in both assignments:

update 
    fk_pay_cal 
set 
    marketplace_fee = 
        (totalprice * commission/100 + shippingcharge + fixed_fee) * (service_tax /100+ 1),
    settlment_value = 
        totalprice 
        - (totalprice * commission/100 + shippingcharge + fixed_fee) * (service_tax /100+ 1);

UPDATE table column with latest related id from the same table

Key to better performance (especially for your original query) is an index with inverted index columns. While being at it, make it UNIQUE:

CREATE UNIQUE INDEX candles_idx1 ON public.candles (minute, day);

Equality column first. See:

• Multicolumn index and performance
• Is a composite index also good for queries on the first field?
• Working of indexes in PostgreSQL

If the index cannot be UNIQUE, you have to tell us more about possible duplicates and how you intend to break ties.

If it can, consider using it as PK to replace the id column (completely). You may want an additional index on (day, minute) ...

While updating all rows, it should be (much) faster to join to a single subquery with the window function lag() in a FROM clause to compute all target values (instead of running a correlated subquery for every row):

UPDATE candles c
SET    id_d1 = c2.prev_id
FROM  (
   SELECT id, lag(id) OVER (PARTITION BY minute ORDER BY day) AS prev_id
   FROM   candles
   ) c2
WHERE  c.id = c2.id

If some rows can already have a correct id_d1, add this line to avoid costly empty updates:

AND    id_d1 IS DISTINCT FROM c2.prev_id

See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

While updating all rows, the index will probably not even be used with the new query.

With the index in place, consider dropping id_d1 from the table completely. Storing functionally dependent values tends to be a bad idea. Computing it on the fly with lag() should be cheap. Then the value is always up to date automatically. Otherwise you have to think about how to keep the column up to date - which may be tricky.

How to update column value based on other column change within same table without any primary key column in table

Please check the documentation: 36.1. Overview of Trigger Behavior

Trigger functions invoked by per-statement triggers should always
return NULL. Trigger functions invoked by per-row triggers can return
a table row (a value of type HeapTuple) to the calling executor, if
they choose. A row-level trigger fired before an operation has the
following choices:

• It can return NULL to skip the operation for the current row. This
  instructs the executor to not perform the row-level operation that
  invoked the trigger (the insertion, modification, or deletion of a
  particular table row).

• For row-level INSERT and UPDATE triggers only, the returned row
  becomes the row that will be inserted or will replace the row being
  updated. This allows the trigger function to modify the row being
  inserted or updated.

A row-level BEFORE trigger that does not intend to cause either of
these behaviors must be careful to return as its result the same row
that was passed in (that is, the NEW row for INSERT and UPDATE
triggers, the OLD row for DELETE triggers).

According to the above you must:

1. declare the trigger as BEFORE UPDATE, not AFTER UPDATE
2. changebuilder_edit_flag column value directly in NEW row instead of firing UPDATE statement

---

CREATE TRIGGER builder_update_trigger_manual_custom_edits
BEFORE UPDATE
ON edmonton.custom_manual_edit
FOR EACH ROW
.....
.....

---

CREATE OR REPLACE FUNCTION 
edmonton.automated_builder_update_trigger_manual_custom_edits()
.....
.....

BEGIN
IF NEW.builder <> OLD.builder THEN
    NEW.builder_edit_flag = 10;
END IF;
RETURN NEW;
.....
.....

---

Related Topics