Alternative for ON DUPLICATE KEY UPDATE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Alternative for ON DUPLICATE KEY UPDATE

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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?
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Web Host using MySQL 3.23 !
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why not just properly handle the "error" that'd come across?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ;)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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 :D
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why not truncate the table and insert all records?
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

His table already has a lot of valuable rows. Cant afford to truncate. Need to insert just new ones
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply