Question 254429 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update statement in postgres sql

I am trying to update the records of one table using others but getting failed with the error. Can someone pls help how to fix the below query for updating records :

ERROR: multiple updates to a row by the same query is not allowed

UPDATE
    table1 t1
    set t1.field1=t2.field1
FROM
    table1 t1 
    INNER JOIN table2 t2 
    ON upper(t1.schemaname)=upper(t2.databasename)
 and upper(t1.tablename)=upper(t2.tablename)
and t1.field1!=t2.field1
WHERE
   t1.field1!='test'
and T2.field1  in('abc','def')
Tags:

2

Answers


  1. 0

    Try this

    -- Step 1: Create the 'table1' and 'table2' tables
CREATE TABLE table1 (
    schemaname VARCHAR(255),
    tablename VARCHAR(255),
    field1 VARCHAR(255)
);

CREATE TABLE table2 (
    databasename VARCHAR(255),
    tablename VARCHAR(255),
    field1 VARCHAR(255)
);

-- Step 2: Insert sample data into 'table1' and 'table2'
INSERT INTO table1 (schemaname, tablename, field1) VALUES
    ('public', 'tableA', 'abc'),
    ('public', 'tableB', 'def'),
    ('public', 'tableC', 'xyz'),
    ('schema1', 'tableX', '123');

INSERT INTO table2 (databasename, tablename, field1) VALUES
    ('public', 'tableA', '123'),
    ('public', 'tableB', 'def'),
    ('public', 'tableC', 'xyz'),
    ('schema1', 'tableX', '456');

-- Step 3: Perform the update
UPDATE table1 AS t1
SET field1 = t2.field1
FROM table2 AS t2
WHERE
    UPPER(t1.schemaname) = UPPER(t2.databasename)
    AND UPPER(t1.tablename) = UPPER(t2.tablename)
    AND t1.field1 != t2.field1
    AND t1.field1 != 'test'
    AND t2.field1 IN ('abc', 'def');
    Login or Signup to reply.
  2. 0

    Use this query, works pretty well,

    UPDATE
    table1 t1
    set t1.field1=t2.field1
FROM
    (select * from table2 where field1 in('abc','def')) t2 
    where upper(t1.schemaname)=upper(t2.databasename)
 and upper(t1.tablename)=upper(t2.tablename)
and t1.field1!=t2.field1
and  t1.field1!='test'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search