On duplicate key query

Question

I'm trying to execute the following sql query on postgres:

INSERT INTO Works_On (ESSN,PNo,Hours) VALUES ('199112111',3,3)
ON DUPLICATE KEY UPDATE Hours = 3;

But when I do I get an error which follows:

psql:test2.sql:2: ERROR:  syntax error at or near "ON"
LINE 2: ON DUPLICATE KEY UPDATE Hours = 3;
        ^

I can't really find the error, help would be most appreciated!

You are using MySQL syntax on Postgres. That is why you are getting the error. — Gordon Linoff
– Gordon Linoff, Commented Mar 15, 2014 at 12:29
with psql it's looks like you are using postgresql. Added the same tag. Moreover there is no ON DUPLICATE KEY UPDATE in postgresql. See this related post which may be of your help stackoverflow.com/questions/1109061/… — Rahul
– Rahul, Commented Mar 15, 2014 at 12:31

Anish Shah · Accepted Answer · 2014-03-15 12:52:57Z

2

ON DUPLICATE KEY UPDATE is not available in postgresql. You can run the following query.

UPDATE Works_on SET Hours = 3 WHERE ESSN='199112111' AND PNo=3;
INSERT INTO table (id, field, field2)
       SELECT '199112111',3,3
       WHERE NOT EXISTS (SELECT 1 FROM Works_on WHERE ESSN='199112111' AND PNo=3);

Here , UPDATE query will succeed if there is already a row with ESSN='199112111' AND PNo=3

INSERT query will succeed if row with ESSN='199112111' AND PNo=3 does not exist.

answered Mar 15, 2014 at 12:52

Anish Shah

8,2099 gold badges31 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

On duplicate key query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related