Update Multiple Rows in Same Query Using Postgresql

Update multiple rows in same query using PostgreSQL

You can also use update ... from syntax and use a mapping table. If you want to update more than one column, it's much more generalizable:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

You can add as many columns as you like:

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

sql fiddle demo

Bulk Update multiple rows in same query using PostgreSQL

In postgresql you can use the update-from-values approach described in the answers here: Update multiple rows in same query using PostgreSQL

And in Go you can implement that like this:

func updateWithSlice(slice []T) error {
    var queryString = `UPDATE "table" AS t SET (
        "column_a"
        , "column_b"
    ) = (
        x."column_a"::text
        , x."column_b"::integer
    )
    FROM (VALUES` // `

    numColumns := 3 // the number of columns you want to update + 1 for the id column
    params := make([]interface{}, len(slice)*numColumns)
    for i, t := range slice {
        pos := i * numColumns

        params[pos+0] = t.ColumnA
        params[pos+1] = t.ColumnB
        params[pos+2] = t.Id

        queryString += `($` + strconv.Itoa(pos+1) +
            `,$` + strconv.Itoa(pos+2) +
            `,$` + strconv.Itoa(pos+3) +
            `),`
    }

    queryString = queryString[:len(queryString)-1] // drop last ","
    queryString += ` ) AS x (
        "column_a"
        , "column_b"
        , "id"
    )
    WHERE t."id" = x."id"::integer` // `

    _, err := db.Exec(queryString, params...)
    return err
}

How to update Multiple rows in PostgreSQL with other fields of same table?

You seem to want:

update t
    set name = username
    where name is null;

Note that -- is not a typical representation of NULL values. You might consider for instance.

How to update multiple rows at the same time with one query

You can do:

update `Bikeshare_data.2019_bike_data`
    set from_station_name = replace(from_station_name, ' (*)', ''),
        to_station_name = replace(to_station_name, ' (*)', ''),
    where from_station_name like '% (*)' or
          to_station_name like '% (*)';

Note: This assumes that ' (*)' does not appear anywhere else in the names. That seems like a reasonable assumption.

SQL UPDATE multiple rows for different IDs

It's quite simple query. You just need update table2.old_id by setting it values from table1.id for rows where their name and address values are equal.

UPDATE table2 SET old_id = (
    SELECT id FROM table1 
    WHERE table1.name = table2.name AND table1.address = table2.address
)

Postgresql: Update multiple rows using JSON input

UPDATE vw_item_status_detail vis 
SET
      quantity = (jst->>'quantity')::numeric,
      status_id = (jst->>'statusId')::numeric
FROM 
      jsonb_array_elements(i_data  -> 'items') as jst
WHERE 
      jst->>'itemId' = vis.item_id;


   INSERT INTO item (
      fulfiller_id,
      item_id,
      order_id,
      status_id,
      sku_code,
      decoration_technology,
      quantity,
      created_time)
   SELECT 
      i_data->>'fulfillerId' fulfillerId, 
      t->>'itemId' itemId, 
      i_data->>'orderId' orderId, 
      1000,
      t->>'skuCode' skuCode,
      t->>'decorationTechnology' decorationTechnology,
      (t->>'quantity')::numeric quantity ,
      NOW()
   FROM jsonb_array_elements(i_data  -> 'items') t
WHERE
 NOT EXISTS (
        select vd.item_id FROM vw_item_status_detail vd WHERE vd.item_id = t->>'itemId'
    );;

Working solution for upsert on views in postgresql

Postgres rule or trigger to update multiple rows after update of single row

Use an AFTER trigger:

create table loc_tbl (id integer, x_loc numeric);
insert into loc_tbl values (1000,12.7), (1500,13.2), (1001,12.7), (1502,13.2), (1002,12.8);

CREATE OR REPLACE FUNCTION public.x_loc_fnc()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.x_loc != OLD.x_loc THEN
        UPDATE 
            loc_tbl
        SET 
            x_loc = NEW.x_loc
        WHERE 
            left(NEW.id::varchar, 3) = left(id::varchar, 3);
    END IF;
RETURN null;
END;

$function$
;

CREATE TRIGGER 
    loc_trg 
AFTER UPDATE ON 
    loc_tbl 
FOR EACH ROW EXECUTE FUNCTION 
    x_loc_fnc();

select * from loc_tbl ;
  id  | x_loc 
------+-------
 1000 |  12.7
 1500 |  13.2
 1001 |  12.7
 1502 |  13.2
 1002 |  12.8

UPDATE loc_tbl SET x_loc = 12.9 WHERE id = 1000;
UPDATE 1

select * from loc_tbl ;
  id  | x_loc 
------+-------
 1500 |  13.2
 1502 |  13.2
 1001 |  12.9
 1002 |  12.9
 1000 |  12.9

Postgres Update Multiple Rows with Multiple Returns Preserving Order

step-by-step demo:db<>fiddle

Joining the records from table_b on table_c needs a common join condition.

Assuming, the ids are correctly increasing with the entrys (so a newer records has a higher id), they can be used as order criterion. If not, you'll need one, because there's nothing like an insert order. Inserting a record does not ensure any query order.

Well, to create the common join condition, you can use the row_number() window function, which creates a row count within an ordered group (= partition). In your case, the partition is the traveler, the order is the id (here table_b):

     SELECT 
         *,
         row_number() OVER (PARTITION BY traveler ORDER BY id)
     FROM table_b;

which yields (notice the last column, which was created)

> id | uuid                                 | idx | location   | traveler                                      | row_number
> -: | :----------------------------------- | --: | :--------- | :-------------------------------------------- | ---------:
>  2 | 676143d6-abe1-48d6-bf78-c72b3186ede3 |   0 | Pittsburgh | 13df1b18-5c6e-4879-9b2f-c78ea215c809          |          1
>  3 | b0cb5e4f-3070-4403-89dd-6063864233c4 |   0 | Denver     | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5          |          1
>  6 | b0cb5e4f-3070-4403-89dd-6063864233c4 |   1 | New York   | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5          |          2
>  5 | 676143d6-abe1-48d6-bf78-c72b3186ede3 |   1 | Las Vegas  | aeae809f-13df1b18-5c6e-4879-9b2f-c78ea215c809 |          1
>  1 | b972acfc-d5b2-4e65-bf31-0a515ee1a2f1 |   0 | Chicago    | f9b0de79-3f38-4cbb-9275-6d68b472e943          |          1
>  4 | b972acfc-d5b2-4e65-bf31-0a515ee1a2f1 |   1 | Charlotte  | f9b0de79-3f38-4cbb-9275-6d68b472e943          |          2

The same can be done with table_c.

Now, you created a normalizing row count on every traveler's records. Because the record numbers equal (which you presumed in your question), this is your join condition:

SELECT
    *
FROM (
    SELECT                                                       -- 1
        *,
        row_number() OVER (PARTITION BY traveler ORDER BY id)
    FROM table_c
) c LEFT JOIN (
    SELECT
        *,
        row_number() OVER (PARTITION BY traveler ORDER BY id)    -- 2
    FROM table_b
) b ON b.traveler = c.traveler AND b.row_number = c.row_number   -- 3

1. table_c with row counts
2. table_b with row counts
3. Join on traveler and row count

Well, if you want to add the table_b's columns uuid and idx to table_c, you need new columns:

ALTER TABLE table_c 
ADD COLUMN b_uuid text,
ADD COLUMN b_idx int;

And then you have to fill them using an UPDATE statement, which uses the query above:

UPDATE table_c c
SET b_uuid = s.b_uuid, b_idx = s.b_idx
FROM (
    SELECT
        c.id,
        b.uuid as b_uuid,
        b.idx as b_idx
    FROM (
        SELECT 
            *,
            row_number() OVER (PARTITION BY traveler ORDER BY id)
        FROM table_c
    ) c LEFT JOIN (
        SELECT
            *,
            row_number() OVER (PARTITION BY traveler ORDER BY id)
        FROM table_b
    ) b ON b.traveler = c.traveler AND b.row_number = c.row_number
) s
WHERE s.id = c.id;

PostgreSQL: Update multiple rows in table with a single query

That's easily done with array operator any():

update car
set status = false
where id = any(array[1, 2, 3])

If id actually is of uuid datatype:

update car
set status = false
where id = any(array[...]::uuid[])

---

Related Topics