SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Tuesday, May 08, 2007

Simple update question

SQL Apprentice Question
I have a simple update query in Sql 2k and want to update only
one field instead of all 3 fields.

Update customer set
Name = @inName,
City = @inCity
State = Case when @instate is not null then @instate else
State end
Where Customer = @inCust

I would like to know what's the best way to prevent not to update all
3 field when updating only one. If I use the case statement then check
for @instate if not null then itself - would this prevent
from updating the third field. Is there a better way to do this
update. Thanks in advance.

Celko Answers

>> The question is by updating all fields [sic] isn't too much overhead for SQL Server 2k to deal with? <<

You missed one of the major points of SQL. The unit of work is the
entire row and not the columns within a row. This is one of the many,
many ways that newbies confuse rows and records, fields and columns.
This is why we have OLD and NEW (called INSERTED and DELETED in local
dialect) in the SQL update model. Records are updated a field at a
time in file systems.

No comments: