[nassau]

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?

[Blue Light Bandit]

UPDATE

How exactly are update queries any more cumbersome than multiple queries with delete and insert?

[nassau]

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


afaik

[Blue Light Bandit]

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"

[pmazer]

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.

[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.

[ast3r3x]

DELETE & INSERT compared to UPDATE is a bad idea. If nothing else because it's more work.

[rollercoaster375]

Yea, DELETE+INSERT is a very bad idea. Good way to screw with auto-increment though.

[nassau]

thanks for all the input guys!

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)

[pmazer]

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.

[nassau]

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.

[Wraven]

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

[ast3r3x]

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.