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

Determining when to use INSERT or UPDATE [message #20668]

Mon, 25 October 2004 02:50

Abraxa

Messages: 72
Registered: August 2004
Location: Germany

Karma: 0

Member

Heya

My problem is that have a table where I add new entries as well as modify existing ones - but what's the smartest way to determine when to use UPDATE or INSERT?

If I use UPDATE for a row id that doesn't exist, the update fails and if I use INSERT the entry I wanted to modify is still around.
That's why I thought about first deleting the row and then inserting it again but that wouldn't be a nice way imo.

Any ideas?

-Soeren

Report message to a moderator

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

Mon, 25 October 2004 11:43

JamesS

Messages: 275
Registered: July 2002
Location: Atlanta, GA

Karma: 0

Senior Member

UPDATE returns an error when it is not successful. If you issue an UPDATE and get an error then issue an INSERT. You may also want to look at REPLACE INTO as another way to do it.

Report message to a moderator

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: 0

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

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

Tue, 26 October 2004 03:20

JamesS

Messages: 275
Registered: July 2002
Location: Atlanta, GA

Karma: 0

Senior Member

It does; MySQL returns an error for the user to decide what to do with it. If you are doing everything through the FUD API then I think the API needs a function like fud_get_last_error().

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 18:51:05 GMT 2024

Total time taken to generate the page: 0.02025 seconds