Update a piece of data in a column using values ​​stored in another SQL table

Question

I am new to SQL and I am trying to perform the following operation:

I have a data column, which we will call it CATEGORY, data in the column:

jack, gill, tommy, jill, jacky, paul, johnny, be, good, tammy, sammy, john

in the second table I have a mapping for writing

column 1 Johnny, be, good - column 2 johnny be good

I need to update the value in CATEGORY for Johnny, be, good to johnny be good , but only in that part of the columns. I tried several things, and below is one of my attempts:

I just tried to select the lines that this happens

 SELECT * FROM [dbo].[TD_DTV_BV_CATALOGITEMS] a INNER JOIN [dbo].[TD_CATEGORYMAP] b ON a.[COURSE_CATEGORY] LIKE b.[GROUPCATEGORY] 

Here I tried updating the column and of course it is invalid.

 UPDATE [dbo].[TD_DTV_BV_CATALOGITEMS] a SET a.COURSE_CATEGORY = REPLACE(a.COURSE_CATEGORY, b.MAPCATEGORY) INNER JOIN [dbo].[TD_CATEGORYMAP] b ON a.[COURSE_CATEGORY] LIKE b.[GROUPCATEGORY] 

Thanks in advance for your help.

Answer 1

Assuming you are on MSSQL, try

SQL Fiddle

Setting up the MS SQL Server 2008 schema :

 create table t ( id int not null identity(1, 1), category varchar(max) ) create table map ( col1 varchar(max), col2 varchar(max) ) insert t (category) values ('jack, gill, tommy, jill, jacky, paul, Johnny, be, good, tammy, sammy, john'), ('jack, gill, tommy, jill, jacky, paul, Johnny, be, good'), ('Johnny, be, good, tammy, sammy, john') insert map (col1, col2) values ('Johnny, be, good', 'johnny be good') 

Request 1 :

 update t set category = replace(category, m.col1, m.col2) from t join map m on t.category like m.col1 + '%' --in the begining or t.category like '%, ' + m.col1 + ',%' --in the middle or t.category like '%, ' + m.col1 --in the end select * from t 

Results :

 | ID | CATEGORY | --------------------------------------------------------------------------------- | 1 | jack, gill, tommy, jill, jacky, paul, johnny be good, tammy, sammy, john | | 2 | jack, gill, tommy, jill, jacky, paul, johnny be good | | 3 | johnny be good, tammy, sammy, john |