FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » General » Database discussions » Determining when to use INSERT or UPDATE
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Determining when to use INSERT or UPDATE [message #20668] Sun, 24 October 2004 22:50 Go to next message
Abraxa is currently offline  Abraxa   Germany
Messages: 72
Registered: August 2004
Location: Germany
Karma: 0
Member
add to buddy list
ignore all messages by this user
Heya Smile

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
Re: Determining when to use INSERT or UPDATE [message #20674 is a reply to message #20668] Mon, 25 October 2004 07:43 Go to previous messageGo to next message
JamesS is currently offline  JamesS   United States
Messages: 275
Registered: July 2002
Location: Atlanta, GA
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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.
Re: Determining when to use INSERT or UPDATE [message #20692 is a reply to message #20668] Mon, 25 October 2004 20:54 Go to previous messageGo to next message
Abraxa is currently offline  Abraxa   Germany
Messages: 72
Registered: August 2004
Location: Germany
Karma: 0
Member
add to buddy list
ignore all messages by this user
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
Re: Determining when to use INSERT or UPDATE [message #20694 is a reply to message #20668] Mon, 25 October 2004 23:20 Go to previous message
JamesS is currently offline  JamesS   United States
Messages: 275
Registered: July 2002
Location: Atlanta, GA
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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().
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Zero values in fields
Next Topic: MySQL 4.1 released
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 15 05:16:31 EST 2017

Total time taken to generate the page: 0.00680 seconds