Page 1 of 1
Alternative for ON DUPLICATE KEY UPDATE
Posted: Wed Mar 30, 2005 11:36 pm
by anjanesh
Im creating a list of SQL Statments in PHP.
I need to have an INSERT statement that'll skip if theres a duplicate key.
ON DUPLICATE KEY UPDATE is only available from MySQL 4.1.0
Any workaround for prev versions ? (MySQL solution not PHP)
Thanks
Posted: Thu Mar 31, 2005 12:21 am
by infolock
On Duplicate is the best that i know of to use, and works exactly the way you seem to need it to. any reason as to why you wish to use an alternative method?
Posted: Thu Mar 31, 2005 1:18 am
by anjanesh
Web Host using MySQL 3.23 !
Posted: Thu Mar 31, 2005 1:23 am
by feyd
why not just properly handle the "error" that'd come across?
Posted: Thu Mar 31, 2005 2:15 am
by timvw
mysql does it like:
update table set ... where ...
if affected_rows <= 0
insert into table values ...
you could make your own updateorinsert($columns) function

Posted: Thu Mar 31, 2005 3:21 am
by infolock
god i couldn't even imagine attempting that

that could either be very fun to learn or a good way for me to get bald really quick..
only thing i know to do is a mix between what feyd and tim said.. except i'd do what you aren't wanting to do and that is use php..
imo, it would just be a lot easier... all ur gonna be doing anyways is one query statement anyways
Code: Select all
$sql = mysql_query("select * from fu where bar = 'fubar'");
$num=mysql_num_rows($sql);
if($num > 0)
{
echo 'entry already exists';
exit;
}
//update table
yeah, i understand that if you have a lot of sql statements it's gonna get long.. but imo it's either that or tell ur web host to update to a newer version and be done with it heh.. otherwise, tell them that ur gonna leave and move to phpwebhosting.com

Posted: Thu Mar 31, 2005 4:38 am
by anjanesh
Well I have a script that creates a sql file with a lot of INSERT statements.
Its this SQL file that I've to give to my client and not a PHP one.
Hes just going to import that file into his db.
Thats why Im looking for pure SQL methods.
Thought of using an IF statement but thats not working either - though its supported since 3.23 -
viewtopic.php?t=31880
Posted: Thu Mar 31, 2005 12:09 pm
by feyd
why not truncate the table and insert all records?
Posted: Thu Mar 31, 2005 12:14 pm
by anjanesh
His table already has a lot of valuable rows. Cant afford to truncate. Need to insert just new ones
Posted: Thu Mar 31, 2005 12:18 pm
by feyd
is this key that may have collisions an auto_increment? If so, pass NULL for that field, the database will make them correct.