I'm from a mssql background so it's difficult to get used to MySQL.
I have a table called
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 (?, ?, ?);
There are two ways to fulfill your request in
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
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 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.