Postgres incorrect update on join

Question

I am trying to run some update queries on Postgres where tables are joined on common field. This is run with SQL Server and the update count is 1 which is expected whereas postgres the update count is 3. It seems that postgres does not perform implicit join when the destination table is the same name as the source table involved in the join. The script could be more descript than what is being said and here it is:

drop table test;
drop table test2;
create table test(col1 int, col2 varchar(10));
insert into test values(1, 'aaa');
insert into test values(2, 'bbb');
insert into test values(3, 'ccc');
create table test2(col1 int);
insert into test2 values(2);
select * from test;
select * from test2;

// Select join = rowcount 1
select count(*) from test t2, test2 t3
where t2.col1 = t3.col1;

// SQL Server update = 1; postgres =3 
update test set col2 = 'changed'
from test t2, test2 t3
where t2.col1 = t3.col1;

The above query can be simplified with:

update test set col2 = 'changed'
from test2 where test.col1 = test2.col1;

but that is not my intention as the join clause might involve some more join statements. The desired intention was to run a query like this:

UPDATE IDMAP_CHILD_JOBID SET RESTORESUCCESS = IDMAP_TABLE_JOBID.RESTORESUCCESS, RESTOREERRMSG = IDMAP_TABLE_JOBID.RESTOREERRMSG
FROM CHILD, IDMAP_TABLE_JOBID
WHERE CHILD.ID = IDMAP_CHILD_JOBID.OLDID AND CHILD.FK1 = IDMAP_TABLE_JOBID.OLDID
AND IDMAP_TABLE_JOBID.RESTORESUCCESS = $FALSE

Postgres complains with table specified more than once if IDMAP_CHILD_JOBID is the same as IDMAP_TABLE_JOBID. How can this be rewritten? My application is supposed to generate an update statement where this is unified query is supposed to be run whereas the behaviour is different. It is obvious that join is performed on select while it is not under update.

Quote from the manual : "Note that the target table must not appear in the from_list, unless you intend a self-join". You need to remove IDMAP_TABLE_JOBID from the FROM part — user330315
– user330315, Commented Nov 18, 2017 at 14:14
some more join statements there is no such thing as a JOIN statement. JOIN is a binary operator, its operands are two table(-expression)s and its result is a table-expression. — wildplasser
– wildplasser, Commented Nov 18, 2017 at 14:41
@wildplasser I digress with that. SQL Server updates can be run JOINs. Terminology might not be used but the equivalence can be obtained similar to SELECT with JOINS. — dmachop
– dmachop, Commented Nov 19, 2017 at 6:01

wildplasser · Accepted Answer · 2017-11-18 14:43:14Z

1

If you need to refer to the same table twice (or more), you need to give them different aliases, so that you can refer to each of them:

update test t1
 set col2 = 'changed'
from test t2    --      <<-- same table !!
JOIN  test2 t3 ON t2.col1 = t3.col1;
where t1.something =t3.something        --<<-- link  the target to the source
        ;

UPDATE idmap_child_jobid dst -- <<-- alias=dst
SET restoresuccess = src.restoresuccess
, restoreerrmsg = i.restoreerrmsg
FROM idmap_table_jobid src --   <<-- same table, different alias=src
JOIN child c ON c.fk1 = src.oldid
WHERE c.id = dst.oldid
AND dst.restoresuccess = false 
        ;

[untested,since I don't know the table definitions,or the intentions of the query]

This is run with SQL Server

SQL-server has a slightly different syntax (and semantics) for updates, in:

UPDATE test
 SET field= 'OMG'
FROM test t1
JOIN othertable t2 ON t1.field = t2.field
    ;

... there are ONLY TWO tables in the range-table; even though testis mentioned twice, it is the same entity) This is a Microsoft/Sybase feature.

edited Nov 18, 2017 at 14:43

answered Nov 18, 2017 at 12:23

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dmachop Over a year ago

Just regurgitating whatever you've said: Postgres update test d set col2 = 'changed' from test t1, test2 t2 where t1.col1 = t2.col1 and d.something = t2.something; SQL Server update d set d.col2 = 'changed' from test d, test t1, test2 t2 where t1.col1 = t2.col1 and d.something = t2.something;

dmachop Over a year ago

I'm writing a method that had to generate such SQL statements and this standard-less UPDATE statement on join seems to be a nightmare! Example: String updateOnJoin(String destTable, Collection<Pair<String, String>> setPairs, Collection<String> fromTables, Collection<Pair<String, String>> joinStatements) every caller had to generate aliases and construct a method which seems to be a difficult way to code :(

Gordon Linoff · Accepted Answer · 2017-11-17 23:20:24Z

1

For this SQL query:

update test
    set col2 = 'changed'
    from test t2, test2 t3
    where t2.col1 = t3.col1;

The equivalent Postgres syntax is:

update test
    set col2 = 'changed'
    from test2 t3
    where test.col1 = t3.col1;

The update/join syntax is different in the two databases (although I think the above will also work in SQL Server).

answered Nov 17, 2017 at 23:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

dmachop Over a year ago

This isn't my intention. It was simplification of what I wanted to do. The final query in my question UPDATE IDMAP_CHILD_JOBID SET RESTORESUCCESS = IDMAP_TABLE_JOBID.RESTORESUCCESS, RESTOREERRMSG = IDMAP_TABLE_JOBID.RESTOREERRMSG FROM CHILD, IDMAP_TABLE_JOBID WHERE CHILD.ID = IDMAP_CHILD_JOBID.OLDID AND CHILD.FK1 = IDMAP_TABLE_JOBID.OLDID AND IDMAP_TABLE_JOBID.RESTORESUCCESS = $FALSE was my intention but there doesn't seem to be a way to use in SQL server and postgres when IDMAP_TABLE_JOBID is the same as IDMAP_CHILD_JOBID. This resorts to alias which have two different syntaxes :(

amacvar · Accepted Answer · 2017-11-18 04:55:39Z

I am a little confused as there seems to be a difference between the Simplified Query (SQry) 2 tables: test,test2 and Final Query (FQry) 3 tables: IDMAP_CHILD_JOBID, CHILD, IDMAP_TABLE_JOBID

To update the single row in SQry: (Note the t1 alias to hopefully prevent table specified more than once error)

update test t1
   set col2 = 'changed'
  from test t2, test2 t3
 where t2.col1   = t3.col1
   and t1.col1 = t3.col1;

So the FQry will update the rows returned from this query and if there are multiple rows in IDMAP_CHILD_JOBID which match CHILD.ID, they will all get updated.

select IDMAP_CHILD_JOBID.RESTORESUCCESS,
       IDMAP_TABLE_JOBID.RESTORESUCCESS, RESTOREERRMSG,
       IDMAP_TABLE_JOBID.RESTOREERRMSG
FROM IDMAP_CHILD_JOBID, CHILD, IDMAP_TABLE_JOBID
WHERE CHILD.ID = IDMAP_CHILD_JOBID.OLDID
  AND CHILD.FK1 = IDMAP_TABLE_JOBID.OLDID
  AND IDMAP_TABLE_JOBID.RESTORESUCCESS = $FALSE

Collectives™ on Stack Overflow

Postgres incorrect update on join

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related