alexc95 alexc95 - 9 months ago 42
MySQL Question


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

I have a table called

. Within this table there are three columns;
is a integer and refers to the user that owns that setting, the
is the name of the setting, the
, 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 = ?;
INSERT INTO users_settings (uid, name, value) VALUES (?, ?, ?);

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?

Answer Source

There are two ways to fulfill your request in MySQL:

  1. 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.

  2. 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.