Skip to Content
0
Former Member
Jun 18, 2016 at 05:58 PM

updating custom columns of target

17 Views

Hi

There are two columns in target table where the value as of now is 'Not Assigned'.

Market_Region = 'Not Assigned',

Market_Name = 'Not Assigned'

Now to these columns i would like to apply below set of update statement using BODS. If it is of one statement like below, i would do it using single lookup but for the multiple statements, what could be the way. Please advise.

--Country

UPDATE POS_Processor

SET

Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

POS_Processor ON

Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE

WHERE

Disty_Electronic_CDF_Markets.State IS NULL AND

Disty_Electronic_CDF_Markets.Zip IS NULL

--State

UPDATE POS_Processor

SET

Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

POS_Processor ON

Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

Disty_Electronic_CDF_Markets.State = POS_Processor.CDF_15

WHERE

Disty_Electronic_CDF_Markets.Zip IS NULL

AND POS_Processor.CDF_15 IS NOT null

--Zip 3 - no state

UPDATE POS_Processor

SET

Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

POS_Processor ON

Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

Disty_Electronic_CDF_Markets.Zip = LEFT(POS_Processor.EC_Ship_To_Postal_Code,3)

WHERE

LEN(Disty_Electronic_CDF_Markets.Zip) = 3

AND Disty_Electronic_CDF_Markets.State IS null

--Zip 1

UPDATE POS_Processor

SET

Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

POS_Processor ON

Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

Disty_Electronic_CDF_Markets.State = POS_Processor.CDF_15 AND

Disty_Electronic_CDF_Markets.Zip = LEFT(POS_Processor.EC_Ship_To_Postal_Code,1)

WHERE

LEN(Disty_Electronic_CDF_Markets.Zip) = 1

AND Disty_Electronic_CDF_Markets.State IS NOT NULL

--Zip 3

UPDATE POS_Processor

SET

Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

POS_Processor ON

Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

Disty_Electronic_CDF_Markets.State = POS_Processor.CDF_15 AND

Disty_Electronic_CDF_Markets.Zip = LEFT(POS_Processor.EC_Ship_To_Postal_Code,3)

WHERE

LEN(Disty_Electronic_CDF_Markets.Zip) = 3

AND Disty_Electronic_CDF_Markets.State IS NOT null

-- Zip 5

UPDATE POS_Processor

SET

Market_Name = Disty_Electronic_CDF_Markets.Market_Name,

Market_Region = Disty_Electronic_CDF_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_Markets INNER JOIN

POS_Processor ON

Disty_Electronic_CDF_Markets.Country = POS_Processor.EC_SHIPTO_COUNTRY_CODE and

Disty_Electronic_CDF_Markets.State = POS_Processor.CDF_15 AND

Disty_Electronic_CDF_Markets.Zip = LEFT(POS_Processor.EC_Ship_To_Postal_Code,5)

WHERE

LEN(Disty_Electronic_CDF_Markets.Zip) = 5

UPDATE POS_Processor

SET

Market_Name = Disty_Electronic_CDF_EMEA_Markets.EC_SHIP_TO_CTRY,

Market_Region = Disty_Electronic_CDF_EMEA_Markets.Market_Region

FROM Trends__CDF.dbo.Disty_Electronic_CDF_EMEA_Markets INNER JOIN

POS_Processor ON

Disty_Electronic_CDF_EMEA_Markets.EC_SHIP_TO_CTRY = POS_Processor.EC_SHIPTO_COUNTRY_NAME

AND Market_Name = 'Not Assigned' AND Market_Region = 'Not Assigned'

AND POS_Processor.EC_REGION_DD1 = 'EMEA'

Thanks,

Abdulrasheed.