i am using MySql Workbench version 6.3.9 with mySql 5.6.35.
i have the following tables:
eID | caochID | eName
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.
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.