sql server data warehouse: Hi,
I am having a problem with my update statement for my
DataMart. (Using example here).
I get text file & load it into TableA in Staging. From
TableA I load it into Dimension TableB as is. Therefore
now I have


TableA

CustomerID Name City Category
---------- ---- ---- -------
ALFKI Maria Berlin A
ANATR Ana Seattle NULL
ANTON Antonio Seattle NULL
AROUT Thomas London B
BERGS Christina Dallas NULL



TableB

CustomerID Name City Category
---------- ---- ---- -------
ALFKI Maria Berlin A
ANATR Ana Seattle NULL
ANTON Antonio Seattle NULL
AROUT Thomas London B
BERGS Christina Dallas NULL



Now I truncate TableA & get a new text file with info.
changed. I load this info in TableA. TableA now has

TableA

CustomerID Name City Category
---------- ---- ---- -------
ALFKI Maria Tokyo A
ANATR Ana Seattle A
ANTON Antonio Seattle A
AROUT Thomas London A
BERGS Christina Dallas NULL



Maria has moved from Berlin to Tokyo. Category col. has
changed. Now I have to update my TableB with changes in
TableA. I write the following update statement but it
doesn't do anything


update B
SET B.Category = A.Category,
B.city = A.city
FROM TableB B, TableA A
WHERE B.customerid = A.customerid
AND b.Category <> a.Category
and b.city <> a.city
--AND b.Category not like a.Category
--and b.city <> not like a.city

I can use the following simple staement but it may be
time consuming & may not be effecient to update more than
200000 records for my Datamart

update B
SET B.Category = A.Category,
B.city = A.city
B.Name = A.Name
FROM TableB B, TableA A
WHERE B.customerid = A.customerid

Please help me with my update statement with above example

Thanks

Steve

hi steve,

following corrections are required in your update statement.

* you are missing OR condition between category matching and city matching.
* null condition for category column is not handled.

--option 1
/*
with OR condition and null condition check for category colun.null condition
is required because any not null and null comparison evaluates to unknown
and hence row is not updated.
*/

update B
SET B.Category = A.Category,
B.city = A.city
FROM TableB B, TableA A
WHERE B.customerid = A.customerid
AND (isnull(b.Category,'') <> isnull(a.Category,'') OR b.city <> a.city)

/*
using isnull will negate the use of indexex on the column hence you can try
following query.
*/

update B
SET B.Category = A.Category,
B.city = A.city
FROM TableB B, TableA A
WHERE B.customerid = A.customerid
AND (b.city <> a.city OR
(a.category <> b.category
OR (a.category is null and b.category is not null)
OR (a.category is not null and b.category is null)))

/*
you can also try following 2 seperate queries individually for category and
city updates.as an option to above query , test it and implement whichever
gives you optimal performance.
*/

update B
SET B.Category = A.Category
FROM TableB B, TableA A
WHERE B.customerid = A.customerid
and (a.category <> b.category
OR (a.category is null and b.category is not null)
OR (a.category is not null and b.category is null))

--if city column is not null then no need to have null check on it.

update B
SET B.city = A.city
FROM TableB B, TableA A
WHERE B.customerid = A.customerid
and (a.city <> b.city)

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com