Updating specific col's based on data availablity

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

General SQL Server Forums

New to SQL Server Programming

Updating specific col's based on data availablity

Author

Topic

Jabez
Starting Member

19 Posts

Posted - 2007-07-20 : 06:20:10

Hi,

DONT KNOW IF IT IS POSSIBLE.. PLEASE SUGGEST.

Currently working on a upload module where in the data from excel file is imported to the destination tables. Data in the excel sheet comes in phases. All excel sheet columns data don't come at first shot. The excel sheet's data is dumped into temporary tables which inturn is looped using cursor's and gets finally updated to the actual tables.

Now, the problem I am facing is how do I update columns of the actual table with the data (i.e NON NULL values) available in the temporary table without tampering the data allready present in actual table.

Ideally what required is, update the actual table column values with the corresponding columns of temporary table ONLY for Non NUll column values of temporary table.

Temporary and Destination tables have 85 columns each. I don't want to write 85 update queries.

The scenario which I am facing is given below with 2 columns as an example.

1. Table 1 :- tbl_source (Temporary Table) has two columns src_Col1 & src_Col2
2. Table 2 :- tbl_destination (Actual Table) has two columns dest_Col1 & des_Col2

Scenario -1
---------------

tbl_Source Sample Data (after excel import to the temporary table)
------------------------

src_Col1 src_Col2
--------- ---------
50 NULL

tbl_Destination Sample Data
------------------------

dest_Col1 dest_Col2
--------- ---------
50 NULL

Scenario -2
---------------

tbl_Source Sample Data
------------------------

src_Col1 src_Col2
--------- ---------
NULL 100

tbl_Destination Sample Data
------------------------

dest_Col1 dest_Col2
--------- ---------
50 100

One update query which handles both scenarios.

Thanking you in anticipation.

Regards

pootle_flump

1064 Posts

Posted - 2007-07-20 : 08:56:46

[code]
UPDATE destination
SET dbo.destination.column_a = COALESCE(dbo.source.column_a, dbo.destination.column_a)
, dbo.destination.column_b = COALESCE(dbo.source.column_b, dbo.destination.column_b)
--... and so on
FROM dbo.destination
INNER JOIN
dbo.source
ON dbo.source.pk_column = dbo.destination.pk_column
WHERE (destination.column_a <> source.column_a OR (destination.column_a IS NULL AND source.column_a IS NOT NULL)
OR
(destination.column_b <> source.column_b OR (destination.column_b IS NULL AND source.column_b IS NOT NULL)
[/code]

Jabez
Starting Member

19 Posts

Posted - 2007-07-20 : 11:45:29

SPECHLESS!!!!

NO WORDS TO EXPLAIN YOUR BRILLIANT SKILLS

THANKS A LOT.

I guess need your help in understanding it thoroughly.

Thanks again.

pootle_flump

1064 Posts

Posted - 2007-07-20 : 17:25:39

Which bit don't you get?

Join of the two tables.
Where clause ensures only rows that will have at least one column updated will be affected. The OR... in the where clause accounts for tri state logic when comparing NULLS.
COALESCE... I'll leave that to you to look up in Books Online

Jabez
Starting Member

19 Posts

Posted - 2007-07-22 : 08:59:24

Thanks. It's clear now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources