CODEWITHSUNDEEP
if-statementsql-updateamazon-redshiftsql-insert
andrea-f
andrea-f

Reputation: 1055

(combine CASE and UPDATE statements) UPSERT in Amazon Redshift

I know with Azure its possible to combine a IF x THEN UPDATE ELSE INSERT, is it possible to do the same thing with Redshift SQL?

I am currently trying this code:

SELECT source,
CASE WHEN (SELECT 1 FROM state WHERE name = 
'out_dev' AND environment = 'dev') > 0
THEN
(UPDATE state SET source='new source')
ELSE (
INSERT state (source, name, load_time, hash, environment)
VALUES ('test', 'out_dev', '2017-10-12 01:14:38', '"f324f873b05d0792a3192bc28f466835"', 'dev'))
END
FROM state

This returns the error:

[Amazon](500310) Invalid operation: syntax error at or near "state" 

Position: 132;

(UPDATE state SET source='new source')

I am new to Redshift so forgive if I am not seeing something obvious.

Upvotes: 0

Views: 1483

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

To answer your first question, Redshift SQL doesn't have control-of-flow statements like "If - Then - Else", so this is not possible the way you are trying to do it.

For your specific example, it's not really clear what you're trying to achieve but if you want to conditionally insert the row for "test" if it doesn't exist then you can do something like:

INSERT INTO state (source, name, load_time, hash, environment)
SELECT 
  'test', 
  'out_dev', 
  '2017-10-12 01:14:38', 
  '"f324f873b05d0792a3192bc28f466835"', 
  'dev'
WHERE NOT EXISTS 
  (
   SELECT 1 FROM state 
   WHERE name = 'out_dev' AND environment = 'dev'
  )
;

Upvotes: 1

Related Questions