MySQL IF statement syntax error

Question

I have this code:

IF ((SELECT COUNT(*) FROM polje WHERE xkoord = 0 AND ykoord = 0) > 0) THEN 
    UPDATE polje 
    SET tezina = tezina + 1
    WHERE xkoord = 0 AND ykoord = 0;

it's suppose to check if a field ('polje') with the coordinates (0,0) exists, and if it does it updates it's weight ('tezina') value by 1. This code is part of a procedure but when I select it out and run just that code I get a syntax error (just like in the procedure).

What am I doing wrong?

EDIT: Here's the whole part of the procedure:

IF ((SELECT COUNT(*) FROM polje WHERE xkoord = x AND ykoord = y) > 0) THEN 
    UPDATE polje 
    SET tezina = tezina + 1
    WHERE xkoord = x AND ykoord = y;
ELSE
    BEGIN
    IF @koncept = 'zid' THEN SET tezina = 9999;
    ELSE SET tezina = 1;
    END IF;
    INSERT INTO polje (xkoord, ykoord, tezina, d_tezina) VALUES (x, y, tezina, tezina);
    END;
END IF;

Where there IS a field already in the database with those coordinates I get a NULL value for 'tezina' instead of tezina = tezina + 1

First of all you are missing END IF; at the end. Can you paste the whole error? — dkasipovic
– dkasipovic, Commented Mar 3, 2014 at 9:14
You're trying to treat a query like a programming statement. — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Mar 3, 2014 at 9:14

paxdiablo · Accepted Answer · 2014-03-03 10:14:32Z

3

I'm not entirely sure why you think you even need the if statement in that first section of code - update is perfectly capable of selecting the records for you:

UPDATE polje 
SET tezina = tezina + 1
WHERE xkoord = 0 AND ykoord = 0;

That will only hit rows where both coordinates are zero. If there are none, then no rows will be modified.

However, if your intent is to detect whether a row exists so you can either insert or update it (as your edit seems to suggest), the normal way of doing that is with insert ... on duplicate key ....

In your particular case, that would be something along the following lines, assuming the primary key was a composite over the coordinate columns:

INSERT INTO polje (
    xkoord, ykoord, tezina, d_tezina
) VALUES (
    x, y, tezina, tezina
) ON DUPLICATE KEY
    UPDATE tezina = tezina + 1

Another possibility, if you don't have your primary key set up that way, is to do the update and detect if you hit any rows. If so, you're done, otherwise you need to insert a row.

The update in that case would be identical to the first one shown above. Then you simply check row_count() to see if the rows updated was zero. If it was, you then insert the first new row for this coordinate pair.

Keep in mind that the definition of "rows updated" depends on how you connect. If you connect with the option CLIENT_FOUND_ROWS, the count is the number of rows touched regardless of the current row value (count is how many rows matched the where clause).

Without that, you only get a count of the rows that were actually changed to something new.

By that, I mean, consider you have two rows:

id   value
--   -----
 1     A
 2     B

and you run update tbl set value = 'A'

The first time you run it without CLIENT_FOUND_ROWS, row count will be set to one since only id 2 is updated. If you run it again, you'll get a row count of zero since no rows are updated.

With CLIENT_FOUND_ROWS, you'll get a row count of two no matter how many times you run it, since it's giving you the rows matched by the where clause.

Also note that the update/detect/insert should be done within a transaction to ensure no race conditions.

edited Mar 3, 2014 at 10:14

answered Mar 3, 2014 at 9:14

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

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

2 Comments

Vedran Karačić Over a year ago

It is because I need to increase the weight of a field if the field exists, and if it doesn't exist it creates one with some initial values.

Vedran Karačić Over a year ago

Hmm, is it somehow possible to use something like on duplicate value ? Because the coordinates are not unique.

xdazz · Accepted Answer · 2014-03-03 09:14:44Z

2

Just use the update clause only:

UPDATE polje 
SET tezina = tezina + 1
WHERE xkoord = 0 AND ykoord = 0

If no record is found by xkoord = 0 AND ykoord = 0, then nothing will happen.

answered Mar 3, 2014 at 9:14

xdazz

161k38 gold badges255 silver badges278 bronze badges

1 Comment

Vedran Karačić Over a year ago

I do need something to happen when there is no record. Please see the edit above.

Collectives™ on Stack Overflow

MySQL IF statement syntax error

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related