How to update only columns that have data

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)

How to update only columns that have data

Author

Topic

poolmwv
Starting Member

12 Posts

Posted - 2007-10-16 : 17:28:26

We have two inventory systems and my task is to get to 1. I have imported from one to the other for years, so now I only have to modify some existing scripts to allow the people to do batch inserts and updates. Because I was getting all the essential data from the "golden" database, all pertinent fields contained data and I could wipe out whatever was in there.

Now the process is different. They might only update one column of data and want only that column of data updated, but not touch the others.

Here's what I have today:

UPDATE ASSET
SET ASSET.ID_CUSTOMER=Asset_Update.OWNER,
ASSET.MFG=Asset_Update.MANUFACTURE_ID,
ASSET.MODEL=Asset_Update.DevModel,
ASSET.TAG_NUMBER=Asset_Update.ASSET_NUMBER,
ASSET.DT_WAR_END=Asset_Update.Warr_End,
ASSET.DT_MAINT_END=Asset_Update.Maint_End,
ASSET.COMMENTS=Asset_Update.Description,
ASSET.NAME=Asset_Update.TermID
FROM ASSET, Asset_Update
WHERE ASSET.SERIAL_NUMBER = Asset_Update.SerNum

How can I change this so that only the columns in the Asset_Update table that have data try to update the columns in the the Asset table?

I'm not an idiot, but I play one on the net.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 00:48:17

You mean that Asset-Update may have columns that are NULL, and those NULLs should not propagate to ASSET table?

If so change:

SET ASSET.ID_CUSTOMER=Asset_Update.OWNER,

to

SET ASSET.ID_CUSTOMER = COALESCE(Asset_Update.OWNER, ASSET.ID_CUSTOMER),

which will take Asset_Update.OWNER if it is NOT NULL, otherwise ASSET.ID_CUSTOMER regardless of whether it is NULL or not.

However, I would have thought what you needed was to have a "copy" of the data from the last transfer of Asset_Update and only update rows/columns where the data for the new Asset_Update was different to the previous. That would allow a column to change from NOT NULL (in last times copy of the data) to NULL (in the new data) and for that NULL value to be propagated to the ASSET table.

I resemble your Signature!

Kristen

poolmwv
Starting Member

12 Posts

Posted - 2007-10-17 : 09:44:40

Woohoo! Thank you! The coalesce is EXACTLY what I was looking for! The Asset_Update table is dropped and created from scratch with a bulk insert of a csv file (e.g. an office moves so the only two columns the data entry people fill out in the spreadsheet are the serial number and the owner), so it's not like it is altered.

I'm mimicking the processes that they are using now because they are fearful of moving to a new system but once they are used to it, I hope to change it so that they only have to run a script to change the owner (in the example given) rather than have to use a spreadsheet.

Thank you, thank you, thank you!

I'm not an idiot, but I play one on the net.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:48:33

You can also use table aliases for easier reading.
And ANSI style JOIN.

UPDATE		a
SET		a.ID_CUSTOMER = COALESCE(u.OWNER, a.ID_CUSTOMER),
		a.MFG = COALESCE(u.MANUFACTURE_ID, a.MFG),
		a.MODEL = COALESCE(u.DevModel, a.MODEL),
		a.TAG_NUMBER = COALESCE(u.ASSET_NUMBER, a.TAG_NUMBER),
		a.DT_WAR_END = COALESCE(u.Warr_End, a.DT_WAR_END),
		a.DT_MAINT_END = COALESCE(u.Maint_End, a.DT_MAINT_END),
		a.COMMENTS = COALESCE(u.Description, a.COMMENTS),
		a.NAME = COALESCE(u.TermID, a.NAME)
FROM		ASSET AS a
INNER JOIN	Asset_Update AS u ON u.SerNum = a.SERIAL_NUMBER

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources