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
– halfer, Commented Jan 30, 2015 at 11:50

Gordon Linoff · Accepted Answer · 2015-01-30 11:53:36Z

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);

axiac · Accepted Answer · 2015-01-30 12:18:47Z

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 · Accepted Answer · 2016-11-23 06:58:09Z

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

Collectives™ on Stack Overflow

IF EXISTS MySQL

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related