2

from existing value Update column using comma-separated values in SQL Server

tbl 
id name rev_code
1  aaa  rev1,rev2,rev3
2  bbb  rev2,rev3
3  ccc  rev1,rev2
4  ddd  rev3,rev2

i want update where rev_code='rev3' need to add end of with , like rev1,rev2,rev3,rev5

expecting output

id name rev_code
1  aaa  rev1,rev2,rev3,rev5
2  bbb  rev2,rev3,rev5
3  ccc  rev1,rev2
4  ddd  rev3,rev2,rev5
Sami Kuhmonen
  • 30,146
  • 9
  • 61
  • 74
mailtoasp
  • 23
  • 1
  • 3

6 Answers6

3

Your best option would be to normalize your database:
Get rid of that rev_code column and turn it into a new table, linked with a foreign key constraint to the current table.

If that's impossible, you can do something like this:

UPDATE tbl
SET rev_code = rev_code + ',rev5'
WHERE ','+ rev_code + ',' LIKE '%,rev3,%'
AND ','+ rev_code + ',' NOT LIKE '%,rev5,%'
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • if rev_code started with rev3 means your like statement fails. – Ponmani Chinnaswamy Apr 21 '16 at 06:34
  • @PonmaniChinnaswamy - No, He has added leading comma in both LHS and RHS – Pரதீப் Apr 21 '16 at 06:35
  • @PonmaniChinnaswamy by adding leading and trailing commas in both sides of the likes I'm ensuring that only correct values would pass. if this was a string of comma delimiter numbers, and you would search for `2`, without that trick you would get false positives on any member that contains `2` - like `21`, `3245` and so on. – Zohar Peled Apr 21 '16 at 06:41
  • Thanks @ZoharPeled. Already rev5 in rev_code means it will add again rev5? – Ponmani Chinnaswamy Apr 21 '16 at 06:44
  • Msg 8152, Level 16, State 13, Line 1 String or binary data would be truncated. The statement has been terminated. error? – mailtoasp Apr 21 '16 at 07:18
  • This is yet another reason why you need to normalize your database. the `rev_code` is probably too short for the newly created strings generated by the update statement. – Zohar Peled Apr 21 '16 at 07:27
1

Stop storing comma separated values in a column it violates First Normal form. Consider changing your database design

At worst case use this update to get the result

UPDATE Yourtable
SET    [rev_code] = [rev_code] + ',rev5'
WHERE  Charindex('rev3', [rev_code]) > 0
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

Try this, this will rev5 for the rev_code if there is rev3 and rev5 already not exists.

UPDATE tbl 
SET    rev_code = rev_code + ',rev5'
WHERE  rev_code NOT LIKE '%rev5%' AND rev_code LIKE '%rev3%'
Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
  • Msg 8152, Level 16, State 13, Line 1 String or binary data would be truncated. The statement has been terminated. error? – mailtoasp Apr 21 '16 at 07:13
0

Try this,

UPDATE table1 SET rev_code=rev_code +',rev5' 
where  rev_code like '%rev3%' and Charindex('rev5', [rev_code])= 0

edited:

 UPDATE table1 SET rev_code= rev_code +',rev5' 
 where rev_code  IS NOT NULL and rev_code like '%rev3%' and Charindex('rev5', [rev_code])= 0

Check rev_code column size.

And as @Zohar Peled said, it is a bad idea while storing comma separated values.

Is storing a delimited list in a database column really that bad?

Community
  • 1
  • 1
Ponmani Chinnaswamy
  • 705
  • 7
  • 21
0

Ouch...you have your db design wrong so i will first suggest to create additional one-to-many table where you would store fields: originalTableRecordId | rev_code

then you find all records in that table that have particular rev_code value and add new one to those that meet criteria.

If you cant change your db structure then you have to go with either "WHERE rev_code like '%rev3%' " (which will be awfully slow) or with fulltext search (which is a huge overkill)

dee zg
  • 13,793
  • 10
  • 42
  • 82
0

how about: 

UPDATE table 
SET rev_code = rev_code+',rev5' 
WHERE rev_code LIKE '%rev3%' (OR RIGHT(rev_code,4) = 'rev3')
gofr1
  • 15,741
  • 11
  • 42
  • 52
thezapper
  • 486
  • 4
  • 13