User Name
Password
AppleNova Forums » Programmer's Nook »

MySQL, UPDATE vs DELETE and INSERT


Register Members List Calendar Search FAQ Posting Guidelines
MySQL, UPDATE vs DELETE and INSERT
Thread Tools
nassau
Member
 
Join Date: Jul 2004
 
2006-01-15, 18:25

UPDATE vs DELETE and INSERT
which method is more desirable and or safe?

UPDATE
pros: seems safer
cons: creating queries is more cumbersome

DELETE and INSERT
pros: creating queries is dead simple
cons: seems less safe (what if INSERT fails, then the whole record is lost)



opinions?
  quote
Blue Light Bandit
Member
 
Join Date: May 2004
 
2006-01-15, 21:55

UPDATE

How exactly are update queries any more cumbersome than multiple queries with delete and insert?
  quote
nassau
Member
 
Join Date: Jul 2004
 
2006-01-15, 22:05

well, don't u have to specify all the names for each field when you update? with INSERT you don't have to.


afaik
  quote
Blue Light Bandit
Member
 
Join Date: May 2004
 
2006-01-15, 22:15

I'm not sure I follow. With UPDATE, you only specify the fields that you intend to change.

ie:

UPDATE users SET usergroup = "banned" WHERE email like "%microsoft.com"
  quote
pmazer
Member
 
Join Date: May 2005
Location: Atlanta, GA
Send a message via AIM to pmazer  
2006-01-15, 23:38

nassau:

You can always use transactions to protect the delete/insert from deleting and not inserting, but I really don't know why you would use that method over updating. So painful to implement and so much slower.
  quote
Gargoyle
http://ga.rgoyle.com
 
Join Date: May 2004
Location: In your dock hiding behind your finder icon!
 
2006-01-16, 04:10

You really haven't got a clue what your doing, have you? The only reasons I can even think that you asked this question are:-

1.) You only have 1 table, so no foreign keys.
2.) You are making your own ID's up, so you can re-use an ID after such an (DELETE/INSERT) operation.

When I am creating a database, almost every table has an ID field, that I create as an unsigned int with auto increment activated.

OK, I have given up keeping this sig up to date. Lets just say I'm the guy that installs every latest version as soon as its available!
  quote
ast3r3x
25 chars of wasted space.
 
Join Date: May 2004
Send a message via AIM to ast3r3x  
2006-01-16, 09:05

DELETE & INSERT compared to UPDATE is a bad idea. If nothing else because it's more work.
  quote
rollercoaster375
Senior Member
 
Join Date: Mar 2005
Location: UIllinois (Champaign, IL)
Send a message via AIM to rollercoaster375 Send a message via MSN to rollercoaster375 Send a message via Yahoo to rollercoaster375 Send a message via Skype™ to rollercoaster375 
2006-01-16, 11:07

Yea, DELETE+INSERT is a very bad idea. Good way to screw with auto-increment though.
  quote
nassau
Member
 
Join Date: Jul 2004
 
2006-01-16, 15:20

thanks for all the input guys!


Quote:
Originally Posted by Gargoyle
You really haven't got a clue what your doing, have you? The only reasons I can even think that you asked this question are:-

1.) You only have 1 table, so no foreign keys.
2.) You are making your own ID's up, so you can re-use an ID after such an (DELETE/INSERT) operation.

When I am creating a database, almost every table has an ID field, that I create as an unsigned int with auto increment activated.

first of all, thank you for helping me Gargoyle, but never ever be condecending to me, it only proves immaturity and stirs up bad feelings. yes, i'm new to this, just like you were once.

second, i have more than one table, and i'm making up my own IDs (no apostrophy please)
  quote
pmazer
Member
 
Join Date: May 2005
Location: Atlanta, GA
Send a message via AIM to pmazer  
2006-01-16, 17:05

Dude, Gargoyle was just trying to help. The only way to get rid of people's misconceptions is to point them out. From Gargoyle's post, that's what it seems like he's trying to do. No one's going to help you if you get defensive when someone tries to help. Also, you're not helping yourself. The only way to learn is to admit your mistakes.
  quote
nassau
Member
 
Join Date: Jul 2004
 
2006-01-16, 17:10

i know he was trying to help, actually hes been helpful to me a few times already. however i felt his post was condecending, and i'm sure most people don't appreciate that when asking for help.

and i was not being defensive at all, reread my posts and you'll see.

  quote
Wraven
Senior Member
 
Join Date: Dec 2004
Location: Texas
 
2006-01-16, 20:26

Quote:
Originally Posted by Gargoyle
You really haven't got a clue what your doing, have you? The only reasons I can even think that you asked this question are:-

1.) You only have 1 table, so no foreign keys.
2.) You are making your own ID's up, so you can re-use an ID after such an (DELETE/INSERT) operation.

When I am creating a database, almost every table has an ID field, that I create as an unsigned int with auto increment activated.
Gargoyle,
What you described is indeed best practice (database / dynamic web sites development is my day job). But I do agree, you were being condescending. People have to start somewhere, and the guy was just looking for help...

Cheers,
Wraven
  quote
ast3r3x
25 chars of wasted space.
 
Join Date: May 2004
Send a message via AIM to ast3r3x  
2006-01-16, 23:55

Well I guess we can let him talk for himself, but I didn't see it as condescending, although it could have been worded nicer.

He was just trying to figure out why you wouldn't use UPDATE. DELETE & INSERT causes some problems and more overhead, and I believe garg just wanted to know the reasoning behind asking about it. I did.
  quote
Posting Rules Navigation
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Post Reply

Forum Jump
Thread Tools

« Previous Thread | Next Thread »

All times are GMT -5. The time now is 23:05.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004 - 2018, AppleNova