FUDforum: Database discussions » Determining when to use INSERT or UPDATE

Home » General » Database discussions » Determining when to use INSERT or UPDATE

Show: Today's Messages :: Polls :: Message Navigator

Re: Determining when to use INSERT or UPDATE [message #20692 is a reply to message #20668]

Tue, 26 October 2004 00:54

Abraxa

Messages: 72
Registered: August 2004
Location: Germany

Karma:

Member

Unfortunately the error that a failed UPDATE returns is caught by FUDforum, hence making this approach not as straightforward as hoped.

I've looked into REPLACE but there's something about it that makes me feel uneasy:

Here follows in more detail the algorithm that is used (it is also used with LOAD DATA ... REPLACE): 
Try to insert the new row into the table 
While the insertion fails because a duplicate-key error occurs for a primary or unique key: 
Delete from the table the conflicting row that has the duplicate key value 
Try again to insert the new row into the table

Doesn't that mean that if the insertion fails the newly inserted row after deletion of the existing one gets an entirely new id (assuming the id field is auto increment)? If that's the case then that will cause problems since other tables refer to entries using their id - however if an update changes the id of an entry then all other tables referring to that entry become corrupt... =\

I also found INSERT...ON DUPLICATE KEY UPDATE but my current version of mySQL (4.0.18 - mentioned statement requires 4.1+) doesn't support it and neither does the version running on the web server.

My current solution is to use a
$rowexists = (db_count(q("SELECT * FROM {SQL_TABLE_PREFIX}memberdata WHERE userid={$usr->id}")) == 1) ? TRUE : FALSE;
and decide on whether to use UPDATE or INSERT from there.

It works, but that scheme is just asking for bugs since it could happen that you forget to update the other branch if you add/remove fields from either the UPDATE or the INSERT clause - I really thought mySQL would have smarter ways to overcome this since I'm sure this is a VERY common problem... =x

-Abraxa

Report message to a moderator

[Message index]

		Determining when to use INSERT or UPDATE By: Abraxa on Mon, 25 October 2004 02:50
		Re: Determining when to use INSERT or UPDATE By: JamesS on Mon, 25 October 2004 11:43
		Re: Determining when to use INSERT or UPDATE By: Abraxa on Tue, 26 October 2004 00:54
		Re: Determining when to use INSERT or UPDATE By: JamesS on Tue, 26 October 2004 03:20

Previous Topic:	Zero values in fields
Next Topic:	MySQL 4.1 released

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 06:46:59 GMT 2024

Total time taken to generate the page: 0.04326 seconds