Uthman Rahimi Uthman Rahimi - 5 months ago 18
SQL Question

Update all rows where contains 5 keys

I have

Ticket
table that has some columns like this :

ID : int
Body : nvarchar
Type : int


I have many rows witch
body
column has value like this :

IPAddress = sometext, ComputerName = sometext , GetID = sometext, CustomerName=sometext-sometext , PharmacyCode = 13162900


I want update all row's
Type
column where the
Body
column has at least 5 keys like :

IPAddress, ComputerName, GetID, CustomerName, PharmecyCode

Answer

You could do it with a simple update statement like that

UPDATE Ticket
SET Type = 4
WHERE Body LIKE '%IPAddress%'
  and Body LIKE '%ComputerName%' 
  and Body LIKE '%GetID%' 
  and Body LIKE '%CustomerName%' 
  and Body LIKE '%PharmacyCode%'

if you know the 'keys' are always in the same order you could concatenate the LIKE conditions like so

UPDATE Ticket
SET Type = 4
WHERE Body LIKE '%IPAddress%ComputerName%GetID%CustomerName%PharmacyCode%'

If you have the possibility to change the data model it would be much better to explode this key & value column into an own table and link it back to this table as it is done in a proper relational model.