IF EXISTS MySQL

Question

I'm from a mssql background so it's difficult to get used to MySQL.

I have a table called users_settings. Within this table there are three columns; uid, name, value. Uid is a integer and refers to the user that owns that setting, the name is the name of the setting, the value, well, you guessed it, is the value.

What I'm trying to do is update these settings if it already exists, but if it doesn't, insert a new row.

My current query is as such (Note I'm using prepared statements):

IF EXISTS (SELECT * FROM users_settings WHERE name = ? AND uid = ?) THEN
                UPDATE users_settings SET value = ? WHERE name = ? AND uid = ?;
            ELSE
                INSERT INTO users_settings (uid, name, value) VALUES (?, ?, ?);
            END IF;

The issue I'm having is that when I attempt to prepare my statement, it returns false, which therefore suggests that the syntax is incorrect. After looking in to this, it looks like it's a SQL syntax error.

Would anybody be able to point me in the relative direction as to what may be occurring here, and where I may have got my syntax incorrect?

Have you tried running this in MySQL manually, to see if it works? If it works there, and it is in the PHP that it fails, we'll need to see the PHP code you are using to prepare/run it. You can check for errors in PHP, and indeed you should - if anything goes wrong in the connect/change-db/prepare/execute/fetch cycle, there is no point in carrying on, and (during dev) you should obtain an error from the driver to see what the issue is. — halfer, Jan 30 '15 at 11:50
http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql — Robert, Jan 30 '15 at 11:51

score 3 · Answer 1 · answered Jan 30 '15 at 11:53

IN MySQL, if as a statement can only used in programming blocks -- stored procedures, functions, and triggers (this is not to be confused with if as a function, which can be used in almost any SQL statement).

You can do what you want in MySQL with a single statement, insert . . . on duplicate key update. For this to work, you need a unique index on name and uid:

create unique index users_settings_name_uid on users_settings(name, uid);

And then:

INSERT INTO users_settings (uid, name, value)
    VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE value = VALUES(value);

Thankyou. I really appreciate the explanation. – alexc95 Jan 30 '15 at 12:29 — alexc95, Jan 30 '15 at 12:29

score 2 · Accepted Answer · answered Jan 30 '15 at 12:18

There are two ways to fulfill your request in MySQL:

If you want to update an existing row or insert a new one if it does not exist then you should use INSERT ... ON DUPLICATE KEY UPDATE:
```
INSERT INTO users_settings (uid, `name`, `value`)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);
```
This relies on an unique index that contains the columns uid and name. If it does not already exist, you can create it:
```
ALTER TABLE users_settings
ADD UNIQUE INDEX uid_name (uid, `name`);
```
You need it anyway because you want a single entry in the table for each user and setting name.
If you want to insert a row in the table and replace (discard) another one that already exists then you can use REPLACE:
```
REPLACE INTO users_settings (uid, `name`, `value`)
VALUES (?, ?, ?);
```
The syntax of REPLACE is similar with the one of INSERT (but it does not support ON DUPLICATE KEY UPDATE for obvious reasons). Internally it does a DELETE followed by an INSERT (it is just a shortcut). It discards the existing row (if any) and insert the new one. It also relies on the presence of the above mentioned index (that you have no matter how you update the values in the table).

For your situation both approaches have the same outcome because there is a single column (value) that is updated or replaced. In other situations only one of them is good.

Choose the one that you feel more appropriate for your workflow and your coding style.

Victor Grados · Answer 3 · 2016-11-23T06:58:09.787

-1

INSERT INTO persona_opcion(nPerCodigo,nOpcCodigo,nPerOpcEstado)
    SELECT '$codPer','$idOpc',1 
    FROM persona_opcion 
    WHERE NOT EXISTS(
       SELECT nPerCodigo,nOpcCodigo,nPerOpcEstado 
       FROM persona_opcion 
       WHERE nOpcCodigo='$idOpc' 
       and nPerCodigo='$codPer'
    )
    LIMIT 1;

enter link description here

edited Nov 23 '16 at 06:58

answered Nov 20 '16 at 06:44

Victor Grados

575
5
6

Please only post in english. – FelixSFD Nov 20 '16 at 08:00

IF EXISTS MySQL

3 Answers3