1

I would like to use an if-then-else structure to decide which of two different commands to run. However, I always get a syntax error when trying to use an if-statement.

The query should run both in MySQL and in postgresql.

IF EXISTS (SELECT * FROM Table WHERE col1 = val1 AND col2 = val2) 
UPDATE Table SET col3 = val3 WHERE col1 = val1 AND col2 = val2 
ELSE 
INSERT INTO Table VALUES (val1, val2, val3) END IF;

What I get is ERROR: syntax error at or near "IF".

I have tried various versions of this, including a 'then' after the if, with or without 'end if', with 'begin' and 'end', but nothing seems to work, so I think I must be getting something very wrong. Other questions on here that I have found always included a SELECT statement in which the if-statements were embedded, which I don't seem to need.

SGer
  • 544
  • 4
  • 18
  • 1
    You need to look into stored procedures, events, and/or triggers to do those kinds of conditionals in MySQL; not sure about Postgres – Uueerdo Mar 07 '16 at 23:49
  • This kind of operations should be done from the application, not on the database level. Anyway, you cannot use IF outside of SELECT steatement. Or as UUeerdo said -> look into stored procedures. – jdabrowski Mar 07 '16 at 23:49
  • 2
    You will not find any syntax for this which works in both MySQL and PostgreSQL, as the standard SQL which they share has no procedural code (conditions, loops, variables, etc). PostgreSQL in particular does not offer any such logic outside of user-defined functions. – IMSoP Mar 07 '16 at 23:50
  • 1
    There is no IF in sql. There is WHERE, though... And RETURNING in postgresql. – wildplasser Mar 07 '16 at 23:51
  • That's called an UPSERT. – Jakub Kania Mar 07 '16 at 23:54
  • In mysql you can use insert ... on duplicate key update ... if you have a pk or unique index on col1 and col2. I do not know how that looks like in postgrsql. – Shadow Mar 07 '16 at 23:56
  • Thanks for pointing out that this is not possible in SQL and telling me that it's called an upsert, that helped to get more information on the problem. I am now trying to just chain an update and an insert, which both use a where-clause to make sure that the update only gets executed on an existing entry and insert only happens when the entry doesn't exist. – SGer Mar 08 '16 at 00:36

1 Answers1

1

OK, with little bit of searching I found the answer.

On mysql use insert ... on duplicate key update ... command. All you need to do is create a pk or unique index on col1 and col2.

In postgresql you can use upsert (insert ... on conflict update ...) to achieve the same result.

Community
  • 1
  • 1
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thanks for doing the research. My problem is that my task is to find a query that solves the problem for both postgresql and mysql. I'm not sure if my instructor has just not thought this through, or if I just haven't found the correct solution yet. – SGer Mar 08 '16 at 00:32
  • These constructs in both products are an extension to sql and therefore are completely product specific. However, if you can use more than one sql statement to achive the results, the use and update followed by insert ... select ... with a not exists clause. The technique is described and discussed in a greater detail in bovin's post on upsert I linked in the answer. – Shadow Mar 08 '16 at 00:40
  • I am allowed to use multiple statements and the answer you linked therefore answers my question. Thank you! – SGer Mar 08 '16 at 01:45