MySQL IF THEN ELSE

Question

I'm trying to make a IF EXISTS UPDATE ELSE INSERT statement but I get errors around the UPDATE line. I could do this in two separate queries but I'm not sure what would be better.

I've tried to follow this guide but didn't manage to make my query work. https://blog.udemy.com/sql-if-then/

IF EXISTS
(
    SELECT 1
    FROM `table`
    WHERE
        `column` = 'value'
)
THEN
(
    UPDATE `table` SET
        `date` = NOW()
    WHERE
        `column` = 'value'
)
ELSE
(
    INSERT INTO `table` SET
        `date` = NOW(),
        `column` = 'value'
)
END IF
END

MySQL is not a programming language like C or Java. That being said, you can write a procedure or function. Where are you executing this "code" at the moment? — Lightness Races in Orbit, Mar 14 '17 at 12:39
@BoundaryImposition: I'm not sure your comment is constructive. `IF` statements are quite common in SQL. `INSERT`/`UPDATE` are common as well. It is just that I can't figure how to write mine. — jimasun, Mar 14 '17 at 12:55
Are u going to insert/update in single query if record exists, Right? If yes, refer this link [insert/update](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — Harshal Yelpale, Mar 14 '17 at 12:56
@HarshalY: I do not have keys in my table so I cannot use `INSERT ... ON DUPLICATE KEY UPDATE`. — jimasun, Mar 14 '17 at 13:02
It might have been more constructive had you answered the carefully-composed question I embedded within it, instead of just complaining. Oh well, good luck. — Lightness Races in Orbit, Mar 14 '17 at 15:05
@BoundaryImposition no need to down vote the question just because I don't get the value of your comment. As for you question, I do not know what you mean by "where". The question remain unanswered but I have solved my problem by running two queries. One to find out if the entry exists. Based on the result I either update either insert. — jimasun, Mar 15 '17 at 08:50
_"no need to down vote the question just because I don't get the value of your comment"_ I didn't. Don't cast aspersions. — Lightness Races in Orbit, Mar 15 '17 at 10:25

score 0 · Answer 1 · answered Mar 14 '17 at 12:39

0

Would this help?

INSERT ... ON DUPLICATE KEY UPDATE

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

In your case "column" would be a unique key

answered Mar 14 '17 at 12:39

Jaydee

4,138
1
19
20

I do not have any (`PRIMARY` or `UNIQUE`) keys set. To know the values of the keys would mean to have the `SELECT` query separately. Edit: I will consider setting `UNIQUE` on `column`. – jimasun Mar 14 '17 at 12:41
My columns are `TINYTEXT` so I cannot use them as keys. – jimasun Mar 14 '17 at 13:00
1

Do you have a particular reason to use TINYTEXT rather than VARCHAR(255)? VARCHAR is ISO standard and as it is stored as part of the row (unlike TINYTEXT) it doesn't have the extra memory overhead. – Jaydee Mar 15 '17 at 10:16
Not really. I'll look into it and will consider converting the columns. – jimasun Mar 15 '17 at 15:14

MySQL IF THEN ELSE

1 Answers1