Postgresql Update Multiple Tables in Single Query

postgresql update multiple tables in single query

This is a CTE thing (but I don't know how to wrap it into a prepared Java-thing)

WITH src AS (
        UPDATE serial_rate
        SET rate = 22.53, serial_key = '0002'
        WHERE serial_key = '002' AND id = '01'
        RETURNING *
        )
UPDATE serial_table dst
SET serial_key = src.serial_key
FROM src
-- WHERE dst.id = src.id AND dst.serial_key  = '002'
WHERE dst.id = '01' AND dst.serial_key  = '002'
        ;

SQL - Update multiple tables, the same column across one query

I think this does what you want. The idea is first update table1, and to use the returning clause to return the table1_id, that can be used to update the two other tables:

with 
    t1 as (
        update table1
        set active = true
        where table1_name = 'Digital Only'
        returning table1_id
    ),
    t2 as (
        update table2
        set active = true
        from t1
        where table1_fk = t1.table1_id
    )
update table3
set active = true
from t1
where table1_fk = t1.table1_id

How to update multiple tables at the same time in Postgresql?

In addition to the solution from Laurenz Albe, you could create an anonymous code block to do this job. Such a query can be very handy when you have many tables and don't want to create one statement per table.

DO $$
DECLARE
  row record;
BEGIN
    FOR row IN SELECT * FROM pg_tables WHERE schemaname = 'public' 
    LOOP
      EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' ADD COLUMN client_created_at bigint, ADD COLUMN client_modified_at bigint;';
      EXECUTE 'UPDATE ' || quote_ident(row.tablename) || ' SET client_created_at = created_at, client_modified_at = modified_at;';
    END LOOP;
END;
$$;

Note: This code block adds the columns you want into all tables in the schema public - use it with care! You can adapt it to the tables you need by changing this query in the block:

SELECT * FROM pg_tables WHERE schemaname = 'public'

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[])

Update multiple tables in one Postgresql

The CTE(with clause) could help to solve your problem, it would implement that update two tables in just one sql. As below:

with tmp1 as (
UPDATE B AS c SET cit_pub_date = '2016' FROM t_metadata AS m WHERE c.meta_id = m.id AND m.id = '2'
)
,tmp2 as (
UPDATE C AS o SET id_originator = 'UN' FROM t_metadata AS m WHERE o.meta_id = m.id AND m.id = '2' returning o.meta_id
)
select * from tmp2

How to update multiple tables with single query

Here is an example using the output clause:

declare @ids table (id int);

update table1
    set status = 1
    output inserted.id into @ids
    where status = 2;

update table2
    set status = 1,
        date = getdate()
    where personid in (select id from @ids);

UPDATE statement with multiple joins in PostgreSQL

The same as valid UPDATE statement in Postgres:

UPDATE incode_warrants iw
SET    warn_docket_no = iv.viol_docket_no
FROM   incode_warrantvs  iwvs
JOIN   incode_violations iv ON iv.viol_citation_no = iwvs.warnv_citation_no
                           AND iv.viol_viol_no = iwvs.warnv_viol_no
WHERE  iw.warn_rid = iwvs.warnv_rid;
-- AND iw.warn_docket_no IS DISTINCT FROM iv.viol_docket_no -- see below

You cannot just use a table alias in the FROM clause as target table in the UPDATE clause. The (one!) table to be updated comes right after UPDATE keyword (if we ignore a possible ONLY keyword in between). You can add an alias there if you want. That's the immediate cause of your error message, but there's more.

The column to be updated is always from the one table to be updated and cannot be table-qualified.

You don't need to repeat the target table in the FROM clause - except for special cases like this:

• PostgreSQL: update with left outer self join ignored

This optional addition can avoid pointless cost by suppressing updates that do not change anything:

AND iw.warn_docket_no IS DISTINCT FROM iv.viol_docket_no

See:

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

More in the excellent manual on UPDATE.

Data insertion into multiple tables with one query using postgres

postgreSql has different syntax for that. I was stuck in this also a few days back figured out how to deal with it. You can user BEGIN block to insert data into multiple tables for that you please follow this query and your work will be done.

BEGIN;
                WITH userins AS (
                INSERT INTO public.user(
                ""FirstName"", ""LastName"", ""Email"", ""PasswordHash"",""VerificationCode"", ""Phone"", ""IsEnabled"", ""IsVerified"", ""IsDeleted"",  ""CreatedDate"")VALUES(@FirstName, @LastName, @Email, @PasswordHash, @VerificationCode, @Phone, @IsEnabled, @IsVerified, @IsDeleted, @CreatedDate)
                    RETURNING id AS user_id)
                ,clientins AS(
                INSERT INTO public.client(""Description"", ""PlanCustomerId"", ""IsActive"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", UserId) 
                        VALUES(@Description, @PlanCustomerId, @IsActive, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT user_id from userins)) RETURNING id as client_id)
                ,clientsubins AS
                (
                   INSERT INTO public.client_subscription(""PlanId"", ""IsActive"",""StartDate"", ""EndDate"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", ClientId, SubscriptionId)
                   VALUES(@PaymentMethodPlanId, @IsActive, @StartDate, @EndDate, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT client_id from clientins), @PlanId)
                )
                ,clientpurchaseins AS
                (
                   INSERT INTO public.client_purchase_history(""PlanId"", ""InvoiceId"",""StartDate"", ""EndDate"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", ClientId)
                   VALUES(@PlanId, @InvoiceId, @StartDate, @EndDate, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT client_id FROM clientins))
                )
                SELECT client_id from clientins;
        COMMIT;

Here is Begin block will take care of your transaction if there is any error occurred then it will be rollback automatically.

---

Related Topics