[SOLVED] convert insert statement to update statement

[trpplayer79]

Dear,

 

I do a import from a sql file into my db.

but when the record already exists I would like to change the statement to a update statement.

I know you can us str_replace but the problem is that there is a different syntax for the 2 statements.

the insert statement syntax is: instert into table ( .., .., .., ) values (..., ..., ..., ...)

the update statement is:UPDATE table SET x=x-data [, y=y-data ...] WHERE where_condition

my problem is that i have no idea how to start the conversion of the insert statement to the update statement.

can someboy please help me (maybe with a standard script)?

 

Greetz

 

Trpplayer79

[MadTechie]

Maybe you should read this MySQL Insert

look for "ON DUPLICATE KEY UPDATE" it may help.. you could use a RegEx to convert Insert to Update,

 

Hope this helps

[trpplayer79]

Sorry but i forgot to say that the input comes from a sql file.

This file is a export from a MsAccess db.

Sow the problem stays the same if i want to use the "insert ... on duplicate key update ..." statement because the update statement needs the standard update syntax.

 

[kenrbnsn]

Change the insert statements to use the alternative format which looks like the update format.

 

Ken

[trpplayer79]

I can not change this in the software I use to export the MsAccess table to the sql file.

does somebody overhere knows a software that does this?

[trpplayer79]

I've found a solution but it isn't fail safe.

this is the code that does the conversion:

$update=trim($query);
$insrt="INSERT INTO Table (field1, field2, .... , remark) VALUES (";
$phrase = str_replace($insrt, "", $update);  /* remove "insert into .. ) values (" from the query to keep only the values */
$newphrase = str_replace(")", "", $phrase); /*removes the ")  at the end  */
$rem_pos = strrpos($newphrase, ", '"); /* get position just befor the remark */
$rem = substr($newphrase, $rem_pos+2); /* create the substring remarks */
/* split the  values into separete variables */
list ($field1value, $field2value, .... , $remark) = split(", ", $newphrase);
/* create the update statement for the different fields */
$upd = "field1".$field1Value.", field2=".$field2value.", ..... ", remarkfield=".$rem."";
/* place the insert statement together with the update statement */
$qry = $update." ON DUPLICATE KEY UPDATE ".$upd;
$query = trim($qry);

 

I had to get the remark with the substr because in the remark the pattern ", ".