CODEWITHSUNDEEP
sql
Nelson Bradley
Nelson Bradley

Reputation: 55

Update part of the data in a column using values that are stored in another SQL table

I am fairly new to SQL and I am trying to do the following operation:

I have a column of data we will call it CATEGORY, the data in the column is as follows:

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

in a second table I have a mapping for the entry

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

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

Here is was just trying to select the rows 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 is where I tried to update 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 any help.

Upvotes: 4

Views: 301

Answers (1)

shibormot
shibormot

Reputation: 1638

assuming you are on MSSQL, try this

SQL Fiddle

MS SQL Server 2008 Schema Setup:

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')

Query 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 |

Upvotes: 1

Related Questions