Insert into unless data already present

[nshack31]

hi, im inserting data into a table, but if the data is already there then i dont want the insert to take place so im using...

foreach ($daylist as $day) { 
 $queryins1="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time') 
 WHERE NOT EXISTS 
(SELECT * FROM coursedates WHERE month = '$monthcal' AND year = '$yearcal' AND day = '".$dayofweek."')";// and not already present 
 mysql_query($queryins1) or die(mysql_error()); 

but this wont work, any ideas?

OR.. how could i do an insert into coursedates where the bookingid from coursedates is not in tblBookings?

thanks

[aspdaddy]

You could just define a primary key on the whole date so that you dont get duplicates.

[nshack31]

i need the date to be available to other courses though as my coursedates table is made up of..

bookingid (primary key)
courseid
day
month
year
time

as different courseid's can share a date, i do not wish duplicate entries to be off in this table

[txbakers]

I like to run a query first on the key to determine if it is in there.

If so, I run an update, if not I run an insert.

[nshack31]

tried that, the mysql affected rows way didnt work, nor did....

//foreach loop here
foreach ($daylist as $day) {
//if bookingid is in bookings update
$queryup="UPDATE coursedates SET courseid = '$courseid', day = '$day', month = '$monthcal', year = '$yearcal', time = '$time' WHERE course = $courseid AND month = '$monthcal' AND year = $yearcal' AND bookingid IN 
   (SELECT bookingid FROM bookings)"; 
mysql_query($queryup) or die(mysql_error());
//else
 $queryins1="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time') WHERE bookingid NOT IN 
   (SELECT bookingid FROM bookings)"; 
 mysql_query($queryins1) or die(mysql_error());
 }

[nshack31]

also tried....

 $queryins1="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time') WHERE bookingid NOT IN 
 (SELECT bookingid FROM bookings)"; 
 mysql_query($queryins1) or die(mysql_error());

on its own without the update :(

and...

alter table coursedates
add unique (courseid, day, month, year, time)

but the unique constraint gives me a duplicate entry error.

[arran]

Using

insert into ignore

ignores any inserts causing duplicate-key violations. Just make sure the key is defined correctly and you will have the desired behaviour.

arran.

[ixyst]

Define a UNIQUE constraint on the year,month,day fields (together) and then use a try..catch statement like this:

try {
'insert ...'
} catch {
'update ...'
}

This assumes that you are more likely to be able to insert the data than not. If you think there will be more updates, you can switch the statement around like this:

try {
'update ...'
} catch {
'insert ...'
}

[Demaestro]

The try and except is the best way to do this. You will get a dupilacate key error when you try to insert something that already exisits.

This assumes however that you have a primary key, and that the record you are trying to insert has that same key. Make sure that you test for the nature of the error, you want to test for the error_type because it could be something like string to long for field varchar(256) or something, don't assume that it will only error when it is a primary key violation.

try:
..insert the record
except:
..if error_value == duplicate key insertion (or whatever your db returns)
....then perform update rather then insert, or skip

[bcolflesh]

[dev.mysql.com...]

[Demaestro]

bcolflesh that is nice, I haven't seen an SQL function like that and know I am thinking of all the places where I have code written and where the example you linked would have done the trick. I am not using MySQL, I use postgreSQL, I had a look but couldn't find any equiv function, does anyone know of a similar function for postgreSQL?

[bcolflesh]

I don't know about a similiar function, but here's a thread with a workaround and good discussion:

[groups.google.com...]