0

I have a table of color schemes that a user can customize and switch between on my site. For instance a 'dark' scheme and a 'light' scheme. I want a way to update this table by first checking which user has which scheme, so I don't add a scheme/user combination twice.

|  userid  |  schemename  |  background  |  text  |
---------------------------------------------------
|    1     |  dark        |  black       |  white |
|    1     |  light       |  white       |  grey  |
|    2     |  dark        |  black       |  white |
|    2     |  light       |  white       |  grey  |
 etc.

I have an update script that keeps these schemes in sync with the master schemes - so if the user happens to delete a scheme they can re-sync their schemes with the master. I need an insert statement that will only add in the scheme if the userid/schemename combination does not already exist in the table. Can I use an INSERT IGNORE or something similar?

Abraham Brookes
  • 1,720
  • 1
  • 17
  • 32

1 Answers1

0

You can do whit a not in clause

insert into   your_destination_table (user_id,  schemename, background, text)
select user_id,  schemename, background, text
from your_source_table 
where (user_id, schemename) not in
   (select user_id,  schemename, background, text
     from your_destination_table);
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • can I combine this with multiple inserts? `INSERT INTO table (keys) VALUES (values), (values), (values) WHERE (userid, schemename) NOT IN (select etc)` – Abraham Brookes Apr 09 '16 at 11:25
  • The number and type of columns in "into table (column,.....)." clause must match with the number and type of columns in "values (columns, ....) ..seems you script don't respect these way ... and for multiple insert the insert ...select script insert all row selected .. ..eventually explain better your need – ScaisEdge Apr 09 '16 at 11:32