0

I have a table that stores userSettings. These settings are generated from a setting table with a default value and the user table. If a user has set something (or changed the default), I store in the userSettings table the custom value against the settingID and userID.

I get the settings thanks to help from this SO question Joining 3 MySQL tables in to 1 query

Now what I would like to do is Insert a record if it does not exist or Update if it does.

I know I can use ON DUPLICATE KEY however I would like to test against userID and settingID which are 2 foreign keys, not test against userSettingID which is the primary, auto increment ID of userSettings table. If there is a match Update, if there is no match Insert.

A user might have up to 20 custom settings so I can't set UNIQUE on the userID column.

Thanks

Community
  • 1
  • 1
puks1978
  • 3,517
  • 9
  • 40
  • 90
  • `on dupe key` will work with **ANY** unique key. it doesn't have to be a primary key. so if you had `(userID, settingID)` as a unique key, then that'd be enough to trigger the on dupe update. doesn't matter if they're foreign keys either. the values in those fields are still "in" the local table. they just have the extra property of having to exist elsewhere as well. – Marc B Nov 03 '15 at 22:06
  • I tried adding UNIQUE to the userID and the settingID columns but because there are multiple userID columns in the userSettings table it failed. – puks1978 Nov 03 '15 at 22:08
  • "multiple userid columns"? Huh? column names are unique in a table. you couldn't have 2+ columns with the same name. multiple records, on the other hand... but if you have multiple copies of a userid/settingid, how do you tell WHICH of the setting values applies? – Marc B Nov 03 '15 at 22:09
  • Sorry, I mean in the userSettings table userID 100 could have settingID 1,2,3,4,5 so therefore in the userSettings table there would be 5 rows for userID 100 each with the settingID and a custom value. However, the settings table could have available settings 1-20 (email notifications, push notifications, public profile pic etc). If a row exists in the userSettings table I am using the custom value but if it does not, I am using the default value from the setting table. – puks1978 Nov 03 '15 at 22:13

1 Answers1

1

This is a MySQL only feature, but you could use REPLACE INTO instead of INSERT INTO. If you have added a UNIQUE index on multiple fields (userID, settingsID) then the query would automatically do as you require.

[EDIT]

From the manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

To add an index to multiple fields (from How do I specify unique constraint for multiple columns in MySQL?):

ALTER TABLE `tbl` ADD UNIQUE `unique_index`(`userID`, `settingsID`);
Community
  • 1
  • 1
soulfreshner
  • 356
  • 1
  • 12
  • The problem I am currently facing is trying to add the UNIQUE on the userID column as there could be multiple rows with a userID but only 1 row containing both userID and settingID combinations. – puks1978 Nov 03 '15 at 22:41
  • @puks1978 surely the combination of userID and settingsID would be unique? – soulfreshner Nov 03 '15 at 22:50
  • Yes the combination can only exist once. I was trying to add UNIQUE to each column, not multiple columns. Thanks. – puks1978 Nov 03 '15 at 22:53