Home SQL normalized data INSERT WHERE NOT EXISTS ; ON DUPLICATE KEY UPDATE
Reply: 0

SQL normalized data INSERT WHERE NOT EXISTS ; ON DUPLICATE KEY UPDATE

user1332
1#
user1332 Published in April 22, 2018, 5:58 pm

i am using MySql Workbench version 6.3.9 with mySql 5.6.35.

i have the following tables:

EQUIPMENT

eID | caochID | eName

COACH

coachID | coachName

SQLfiddle prepared http://sqlfiddle.com/#!9/e333d/1

eID is a primary key. there are multiple coachID's in different equipment, so there will be duplicate coachIDs with different equipment, but the eID will be unique as it is a primary key.

REQUIRED i need to insert a row in the equipment table, if it does not already exist. If it exists, do nothing.

various posts online have pointed me towards two options:

a) INSERT...ON DUPLICATE KEY UPDATE...

b)INSERT...WHERE NOT EXISTS

PROBLEM i have problems with both of these solutions. for the first solution (ON DUPLICATE KEY UPDATE) the query inserts the row as required but does not update the existing row. instead it creates a new entry. for the second solution (WHERE NOT EXISTS) i get an error : SYNTAX ERROR: 'WHERE' (WHERE) is not a valid input at this position.

the sql query doesnt need to make any joins. i listed both tables so that you can see how they are related. the insert query i need will only insert for the equipment table.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.394755 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO