update fields only if filled in

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update fields only if filled in

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-09 : 07:36:38

i pass into my stored procedure
Eid
@firstname
@lastname
@address
@city

I want to run an update
update customers set firstname=@firstname,lastname@lastname,address=@address,city=@city where customerid=@id

but i only want to update the fields that are not blank
so if firstname is blank it won't update to blank but will leave what's currently there
what's the best way?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-09 : 08:05:34

SET FirstName = ISNULL(Firstname,@firstname)

Will replace a NULL FirstName column with the variable.

Poor planning on your part does not constitute an emergency on my part.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-09 : 08:45:10

i don't think this is right
i want if the @firstname is null it should use whatever is in firstname
if @firstname is not null it replaces with @firsname

same with other fields.

ISNULL (check_expression ,replacement_value )

is null seems to check if the field in the table is null and then replace but i want the opposite

will it work if i do

SET FirstName = ISNULL(@Firstname,firstname)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 13:00:16

it will work. also you might need a WHERE condition to avoid the update in case of NULL as you say like


...
where customerid=@id
and (@firstname IS NOT NULL
or @lastname IS NOT NULL
or @address IS NOT NULL
or @city IS NOT NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-10 : 00:26:50

no but I want each field to only update if the passed variable is not null so it can update firstname and not last anme if firstname is passed but not last name.
do I need to make a separate update query for each field -- I don't want it to only update if all are not null but each field passed should be updated. (and updated with the new fieldd if it's not null)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:27:14

thats not possible. you can control update of a row but within that you cant control the updates on individual columns. best you can do is using a case when or isnull making it retain value it already has.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-10 : 12:31:24

i did the isnull as above and it seemed to work (without the where clause)
there is always at least one field filled in
did i do something wrong as it seemed to work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 12:39:39

it will work with ISNULL what i told was it will still do update on all columns.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources