IF EXISTS MySQL syntax error, but each line works individually

Question

I have a database in place with a couple thousand promo codes, and I need something implemented where when a worker goes to get an ID it will check their ID if it is already assigned to a promo code, will return it, if there ID is not in the system will return the next promo code in the queue. This code I have now works each line individually but not as whole. I feel like its a simple error but I need a second pair of eyes looking onto it

IF EXISTS(select promo_code from Promo where worker_id='ABC123') select promo_code from Promo where worker_id='ABC123' ELSE insert into Promo(worker_id) values('ABC123') where Id=count(worker_id)+1

Sample Data

Id worker_id promo_code 0 ABC123 1234567890 1 1928374657 2 9184373462

So if ABC123 was passed it would return me 1234567890, but if I passed it DEF456, it would then see DEF456 is not in the table, insert it in the table at Id=1

Your query doesn't make sense to me. Can you provide some sample data and what you want to accomplish? — Gordon Linoff, Mar 12 '14 at 20:27
You IF syntax is wrong. Possible duplicate of http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists — Galz, Mar 12 '14 at 20:46

Justin Ryder · Answer 1 · 2014-03-13T18:39:12.607

0

EDIT: This is T-Sql, I read the question too fast and didn't notice that OP was using MySQL.

You need to begin and end when using an if statement.

IF EXISTS(select promo_code from Promo where worker_id='ABC123')
BEGIN
  select promo_code from Promo where worker_id='ABC123'
END
ELSE
  insert into Promo(worker_id) values('ABC123')
    where Id=count(worker_id)+1

http://technet.microsoft.com/en-us/library/ms182717.aspx

**Don't ask me why begin and end only go around the if part; I have no clue why the else doesn't need it also...

edited Mar 13 '14 at 18:39

answered Mar 12 '14 at 20:42

Justin Ryder

757
9
17

Still getting error "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 EXISTS(select promo_code from Promo where vendor_id='ABC123') BEGIN selec' at line 1" – user3273370 Mar 12 '14 at 20:52
Oops, my bad, this is T-Sql that I posted. I read the question too fast. – Justin Ryder Mar 13 '14 at 18:38

Gordon Linoff · Answer 2 · 2014-03-13T01:38:10.133

0

You seem to be looking for an update, not an insert. You can do the update as:

update table t
    set worker_id = 'ABC123'
    where not exists (select * from (select 1 from table t2 where worker_id = 'ABC123') t) and
          worker_id is null
    order by id
    limit 1;

Then return the value you want is:

select promo_code
from table t
where worker_id = 'ABC123';

In the update, the not exists is essentially saying "don't do anything if the worker id is already in the table". The double subquery is to get around a quirk of MySQL. I would also recommend a unique index on worker_id, because you do not seem to want duplicates in the table:

create unique index table(worker_id);

Once the record is updated, you can fetch the value using the select.

edited Mar 13 '14 at 01:38

answered Mar 12 '14 at 20:58

Gordon Linoff

1,242,037
58
646
786

Getting 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 'table `Promo` where `worker_id` = 'ABC123')) and `worker_id` is null ' at line 3 – user3273370 Mar 13 '14 at 01:35
@user3273370 . . . That MySQL trick requires a table alias as well. – Gordon Linoff Mar 13 '14 at 01:38

IF EXISTS MySQL syntax error, but each line works individually

2 Answers2