CODEWITHSUNDEEP
sql-servert-sql
DataDoer
DataDoer

Reputation: 209

SQL update issue - Update single value multiple times

I'm using SQL Server 2014.

I have this table: Say T1. Only 2 columns.

RecID    Desc
-----------------------------------------------
5        An apple a day keeps the doctors away.

And another table, say T2:

RecID_FK   Word
--------------------------
   5       apple
   5       doctors

I want to update T1.Desc to get rid of associated words on T2 (that's "apple" & "doctors")

This is my SQL:

UPDATE A
SET A.Desc = LTRIM(RTRIM(REPLACE(' ' + A.Desc + ' ',' ' + B.Word + ' ', ' '))) 
FROM T1 As A
INNER JOIN T2 As B ON A.RecID = B.RecID_FK

This doesn't work well.

It removes "apple", but "doctors" is still there.

Could you please provide me correct SQL to achieve this?

Upvotes: 1

Views: 75

Answers (2)

radar
radar

Reputation: 13425

you need to do it using cursor or while loop

here is one more method using recursive cte

The recursive cte keeps updating words in the description till all words are replaced

the last sentence where all words are replaced is used to update the table

;with cte
as
( select RecID_FK, word,
  ROW_NUMBER() over ( partition by recID_FK order by ( select null)) as rn,
  COUNT(*) over ( PARTITION by recID_FK ) as cnt
  from t2

)
,cte1
as
( select recid , LTRIM(RTRIM(REPLACE(' ' + t1.[Desc] + ' ',' ' + cte.Word + ' ', ' '))) as [Desc] , 2 as n, cnt 
  from t1
  join cte
  on t1.RecID = cte.RecID_FK
  and cte.rn =1
  union all
  select recid, LTRIM(RTRIM(REPLACE(' ' + cte1.[Desc] + ' ',' ' + cte.Word + ' ', ' '))) , n+1, cte.cnt
  from cte1 
  join cte 
  on cte1.recID = cte.RecID_FK
  and cte.rn = n
)
update t1
set [Desc] = cte1.[desc]
from t1
join cte1
on cte1.RecID = t1.RecID
and cte1.n = cte1.cnt +1

Upvotes: 1

Ron Smith
Ron Smith

Reputation: 3266

It will only update the first join match. Try this:

while @@ROWCOUNT > 0
    begin
        UPDATE A
        SET A.Desc = LTRIM(RTRIM(REPLACE(' ' + A.Desc + ' ',' ' + B.Word + ' ', ' '))) 
        FROM T1 As A
        INNER JOIN T2 As B ON A.RecID = B.RecID_FK
            and A.Desc like '% ' + B.Word + ' %'
    end

Upvotes: 1

Related Questions