MySQL if() with select statement

Question

I have no idea what I'm missing here:

IF (SELECT 'Nazwa' FROM stanowiska WHERE Nazwa LIKE = 'Lakiernik') IS NULL THEN  
INSERT INTO stanowiska (Nazwa) VALUES ('Lakiernik')
ELSE   
SELECT Nazwa FROM stanowiska WHERE Nazwa LIKE 'Lakiernik'

Any help would be appreciated

What are you trying to do? LIKE = isn't valid syntax, for instance. — Gordon Linoff
– Gordon Linoff, Commented Oct 24, 2015 at 15:34
I want to check if the record exists. If no, I want to insert it. If yes, I just want to select it. — Marox
– Marox, Commented Oct 24, 2015 at 15:35

Gordon Linoff · Accepted Answer · 2015-10-24 15:38:22Z

3

This might be what you want:

IF NOT EXISTS (SELECT 1 FROM stanowiska WHERE Nazwa = 'Lakiernik') THEN  
    INSERT INTO stanowiska (Nazwa)
        VALUES ('Lakiernik');
ELSE SELECT Nazwa FROM stanowiska WHERE Nazwa = 'Lakiernik'
END IF;

Even this is really strange. The code block is inserting in the THEN and return a value in the ELSE. That doesn't seem right. And, the part being returned is just Lakiernik, so I don't think the ELSE is needed:

IF NOT EXISTS (SELECT 1 FROM stanowiska WHERE Nazwa = 'Lakiernik') THEN  
    INSERT INTO stanowiska (Nazwa)
        VALUES ('Lakiernik');
END IF;

However, this has race conditions. So, if you want to prevent duplicates, the right way is:

create unique index unq_stanowiska_nazwa on stanowiska(nazwa);

Then:

    INSERT INTO stanowiska (Nazwa)
        VALUES ('Lakiernik')
        ON DUPLICATE KEY UPDATE nazwa = VALUES(Nazwa);

answered Oct 24, 2015 at 15:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Marox Over a year ago

Thanks a lot for that answer. Works perfect

Luis Teijon · Accepted Answer · 2015-10-24 16:20:28Z

0

Try this, didn't test it yet:

IF (SELECT 1 FROM stanowiska WHERE Nazwa = 'Lakiernik') 
THEN  
INSERT INTO stanowiska (Nazwa) VALUES ('Lakiernik');
ELSE   
SELECT Nazwa FROM stanowiska WHERE Nazwa ='Lakiernik';
END IF;

edited Oct 24, 2015 at 16:20

answered Oct 24, 2015 at 15:38

Luis Teijon

4,9297 gold badges39 silver badges60 bronze badges

1 Comment

Marox Over a year ago

it shows an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (SELECT 1 FROM stanowiska WHERE Nazwa = 'Lakiernik') THEN INSERT INTO stan' at line 1

Collectives™ on Stack Overflow

MySQL if() with select statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related