Update column from column from another table partially matched-CodePudding

I have two tables, the first table is called IM_ITEM and has the following columns

ITEM_NO	DESCR	BRAND
10000	Orin Swift Black	" "
10001	Gold Peak Sweet tea	" "
10002	Vara Red Blend	" "
10003	Gatorade Lemon Lime	Gatorade

The second table is a lookup table called IM_ATTR_COD and has the following columns

ATTR_5	Description
Orin Swift	Orin Swift
Gold Peak	Gold Peak
Vara	Vara
Gatorade	Gatorade
Comfort	Southern Comfort

I want to update the empty field "BRAND" in table "IM_ITEM" by data from column "ATTR_5" in table "IM_ATTR_COD". When ATTR_5 partially matches data in "DESCR" from table IM_ITEM

I used the following code, but it did not work properly

UPDATE IM_ITEM
SET BRAND = ATTR_5
FROM IM_ITEM M
RIGHT JOIN IM_ATTR_COD R ON M.BRAND = R.ATTR_5
WHERE CHARINDEX (R.ATTR_5, M.DESCR) > 0

Expected Result

ITEM_NO	DESCR	BRAND
10000	Orin Swift Black	Orin Swift
10001	Gold Peak Sweet tea	Gold Peak
10002	Vara Red Blend	Vara
10003	Gatorade Lemon Lime	Gatorade

Any help, please

CodePudding user response：

Try

UPDATE IM_ITEM
SET BRAND = ATTR_5
FROM IM_ITEM M
INNER JOIN IM_ATTR_COD R ON M.DESCR LIKE '%'   R.ATTR_5   '%'
WHERE (M.BRAND = '' OR M.BRAND IS NULL)

In case you want it to apply to all rows, just remove WHERE (M.BRAND = '' OR M.BRAND IS NULL)

CodePudding user response：

Without concat then:

UPDATE m
SET m.BRAND = r.ATTR_5
FROM IM_ITEM m
JOIN IM_ATTR_COD r
    ON m.DESCR LIKE r.ATTR_5   '%'
WHERE ISNULL(m.BRAND,'') = ''

CodePudding user response：

You can use LIKE clause and use the same in JOIN, as given below.

DECLARE @im_item table (ITEM_NO int, DESCR  varchar(500), BRAND varchar(50))
DECLARE @im_attr_cod table (attr_5 varchar(100), DESCR  varchar(500))
INSERT INTO @im_item 
values
(10000,'Orin Swift Black',  null)
,(10001,'Gold Peak Sweet tea',null);
INSERT INTO @im_attr_cod
values 
('Orin Swift',  'Orin Swift')
,('Gold Peak',  'Gold Peak');
UPDATE i 
SET BRAND = a.DESCR
FROM @im_item AS i
INNER JOIN @im_attr_cod as a
on i.DESCR like a.attr_5   '%';
SELECT * FROM @im_item

ITEM_NO	DESCR	BRAND
10000	Orin Swift Black	Orin Swift
10001	Gold Peak Sweet tea	Gold Peak