CODEWITHSUNDEEP
mysqlsqlif-statementconditional-statementscase
Gene Vincent
Gene Vincent

Reputation: 5469

MySQL IF deciding multiple fields?

Depending on a complicated condition, I need to insert one or the other set of fields.

What I would like to do is (pseudo code)

IF (condition) DO
  INSERT INTO table set columnA= "foo", columnB = "bar"
ELSE
  INSERT INTO table set columnA= "no foo", columnB = "no bar"
ENDIF

Unfortunately I can't use stored procedures and I have to fit this into a regular statement.

I could repeat the condition for every column

INSERT INTO table (columnA, columnB) SELECT IF(condition, "foo", "no foo"), IF(condition, "bar", "no bar")

But the conditiion itself is complex and nested, so I'm trying not to repeat it.

Is there a syntax that lets a single IF or CASE decide multiple columns ?

Upvotes: 1

Views: 2077

Answers (4)

Turophile
Turophile

Reputation: 3405

What about

  INSERT INTO table 
    SELECT "foo", "bar" FROM something WHERE (condition)
    UNION
    SELECT "no foo", "no bar" FROM something WHERE NOT(condition)

Upvotes: 0

Adam
Adam

Reputation: 18855

Variables are your friends ;-)

 INSERT INTO table (columnA, columnB)
 SELECT IF(@condition:=<your condition goes here>, "foo", "no foo"),
        IF(@condition, "bar", "no bar");

Not that you can use EXISTS(SELECT something) as a valid condition

Upvotes: 1

Marcus Adams
Marcus Adams

Reputation: 53870

You do get a WHERE clause with a SELECT ... FROM:

You can break it into two separate queries, but, it's two queries:

INSERT INTO table (columnA, columnB)
SELECT 'foo', 'bar'
WHERE :foo;

INSERT INTO table (columnA, columnB)
SELECT 'no foo', 'no bar'
WHERE NOT :foo;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Not exactly. But it would appear that your condition is independent of the rows. You could do something like this:

INSERT INTO table(columnA, columnB)
    select (case when flag = 1 then 'foo' else 'no foo' end),
           (case when flag = 1 then 'bar' else 'no bar' end)
    from (select <condition> as flag) x;

Upvotes: 1

Related Questions