1

What would be the syntax for doing the following:

INSERT INTO table
SET 
IF *expression*
column1 = 'value1'
ELSE
column2 = 'value2'
user2180613
  • 739
  • 6
  • 21

1 Answers1

2

You would use insert . . . select with a case statement:

INSERT INTO table(column1, column2)
    select (case when *expression* then 'value1' end) as column1,
           (case when not *expression* then 'value2' end) as column2;

However, I suspect that you might really want an update and not an insert:

update table
    set column1 = (case when *expression* then 'value1' else column1 end),
        column2 = (case when not *expression* then 'value2' else column2 end);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Although it works, the same expression has to be evaluated twice. Is there a way to avoid such behaviour (for performance reasons)? – user2180613 Sep 09 '13 at 16:33
  • @user2180613 . . . Unless the expression involves a subquery, then multiple evaluation should not be an issue. If it does, I would ask you to modify your question with the query, sample data, and expected results. The `insert`/`update` operation is much more expensive. – Gordon Linoff Sep 09 '13 at 16:37
  • Evaluations may be simple, but will be performed on several millions of rows. – user2180613 Sep 09 '13 at 16:40