complex table join in sql server 2014

Question

complex table join in sql server 2014

Uma 446

I am trying to update target table column brand code using source table column Brand_code based on the long_item_number and chain

but long_item_number in source contain hyphen where as Target table long item is not having hypen

Chain CM in source is equivalent to 7 in target, similarly EPRC equivalent to 1, FSC equivalent to 3.

sample data:

DECLARE  @SRC_SOURCE TABLE
(
Chain varchar (10),
Long_item_number varchar (100),
product_name varchar (500),
Brand_code varchar(500)
)
INSERT @SRC_SOURCE
SELECT 'CM',	'30041000300254600000',	'Hynix',	'Samsung COLLECTION' UNION ALL
SELECT 'EPRC',	'35510100000090000000',	'cd-100',	'Samsung COLLECTION' UNION ALL
SELECT 'FSC',	'00610010000010000024',	'KNIT',	NULL UNION ALL
SELECT 'FSC',	'01530060040250000000',	'4025-31',	'' UNION ALL
SELECT 'FSC',	'01530060040100000000',	'4010-31',	'' UNION ALL
SELECT 'FSC',	'01530060040120000000',	'4012-31',	NULL 
--SELECT * FROM @SRC_SOURCE
DECLARE  @TGT_TARGET TABLE
(
Chain INT,
Long_item_number varchar (100),
product_name varchar (500),
Brand_code varchar(500)
)
INSERT @TGT_TARGET
SELECT 7,	'3004-10003-0025-460-0000',	'7.5. Score', 'Samsung	Galaxy' UNION ALL
SELECT 1,	'3551-01000-0009-000-0000',	'7.5. Score', 'Samsung	Galaxy' UNION ALL
SELECT 3,	'0061-00100-0001-000-0024',	'8.3. Score', 'Samsung	jbl' UNION ALL
SELECT 3,	'0153-00600-4025-000-0000',	'8.3. Score', 'Samsung	Ultra' UNION ALL
SELECT 3,	'0153-00600-4010-000-0000',	'8.3. Score', 'Samsung	Ultra' UNION ALL
SELECT 3,	'0153-00600-4012-000-0000',	'7.5. Score', 'Samsung	flip3'
SELECT * FROM @TGT_TARGET

Can Any one please help me how to use case in join condition for Chain and how to take care of hyphen in key column long_item_id.

Code i tried but not giving result.


update t
set t.brand_code = s.brand_code
from source s
    inner join target t on
       s.long_item_number = t.long_item_number
and s.chain = t.chain

Thanks a lot

Accepted answer

1 additional answer

Your answer

Answer 1

Hi @Uma

Try this:

update t
set t.Brand_code = s.Brand_code
from @SRC_SOURCE s inner join @TGT_TARGET t 
  on s.Chain = CASE t.Chain WHEN 1 THEN 'EPRC'
                            WHEN 3 THEN 'FSC'
                            WHEN 7 THEN 'CM' END
 and s.Long_item_number = replace( t.Long_item_number, '-', '')

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Viorel 122.6K

Check a statement:

update t
set t.Brand_code = s.Brand_code
from @SRC_SOURCE s
inner join @TGT_TARGET t on s.Long_item_number = replace( t.Long_item_number, '-', '')
--and s.Chain = t.Chain

Share via

complex table join in sql server 2014

1 additional answer

Your answer