Update Sql Query ?

[AukI]

Hi guys ,

I want to create manual update , delete and insert command... I am curious why ms create those line of commands:

UPDATE       Department
SET                ID = ?, Department = ?
WHERE        (ID = ?) [B][COLOR="red"]AND (? = 1)[/COLOR][/B] AND [COLOR="red"][B](Department IS NULL) OR[/B][/COLOR]
                         (ID = ?) AND (Department = ?)

above codes , red blod lines i don't understand , it should be simple like :

UPDATE       Department
SET            ID = ?, Department = ?
WHERE        (ID = ?) AND (Department = ?)

where id is the primary key.

 

[cjard]

It's called "Optimistic Concurrency"

The "complex" query will oNLY update the row if noone else edited it during the time between you downloading it and you presenting it for update again.

Consider:

You and I both download Person ID 1 - John
I change the name to Tim
You change it to Joe
I save it:

UPDATE people SET name = Tim WHERE ID = 1 AND name = John 'this is put here because .net remembers the original value

It updates 1 record

You try to save:

UPDATE people SET name = Joe WHERE ID = 1 AND name = John 'this is bold bit is NOW FALSE

0 records are updated
Your program encounters a ConcurrencyViolationException
You present a message to the user "Someone else has changed the record youre trying to edit, refresh the record and make your changes again"


There are cleverer things to do at this point, like merging the changes.. The thing I'm pointing out is that the complex query protects you from blindly updating records that have changed - it saves on the need to lock records which can cause deadlocks

You need the IS NULL checks in there because "name = NULL" is ALWAYS false, so if the field is ever set null it cannot be updated unless the query includes protection for this

In short: don't worry about it. Some clever beards at microsoft got paid a lot of money to make this work and it does what it does for a good reason that you shouldnt mess with if youre not fully understanding it..

 

[AukI]

"Optimistic Concurrency"

Thank you for your reply...I really do understand . It really helps a lot. Thank you for your time and afford.

But in some portion i got lost.

There are cleverer things to do at this point, like merging the changes.. The thing I'm pointing out is that the complex query protects you from blindly updating records that have changed - it saves on the need to lock records which can cause deadlocks

You need the IS NULL checks in there because "name = NULL" is ALWAYS false, so if the field is ever set null it cannot be updated unless the query includes protection for this

Can you tell me more clearly how to mearge records in query... and " IS NULL checks in there because "name = NULL" " where should I check this null and why? doesn't make more clear sense... but I hope you can explain it lot..


You made a great post..

Thanks again.

It's called "Optimistic Concurrency"

The "complex" query will oNLY update the row if noone else edited it during the time between you downloading it and you presenting it for update again.

Consider:

You and I both download Person ID 1 - John
I change the name to Tim
You change it to Joe
I save it:

UPDATE people SET name = Tim WHERE ID = 1 AND name = John 'this is put here because .net remembers the original value

It updates 1 record

You try to save:

UPDATE people SET name = Joe WHERE ID = 1 AND name = John 'this is bold bit is NOW FALSE

0 records are updated
Your program encounters a ConcurrencyViolationException
You present a message to the user "Someone else has changed the record youre trying to edit, refresh the record and make your changes again"


There are cleverer things to do at this point, like merging the changes.. The thing I'm pointing out is that the complex query protects you from blindly updating records that have changed - it saves on the need to lock records which can cause deadlocks

You need the IS NULL checks in there because "name = NULL" is ALWAYS false, so if the field is ever set null it cannot be updated unless the query includes protection for this

In short: don't worry about it. Some clever beards at microsoft got paid a lot of money to make this work and it does what it does for a good reason that you shouldnt mess with if youre not fully understanding it..