0

I'm trying to run an update statement based on the contents of a For XML PATH field. The SQL is:

UPDATE a
SET a. ItemsList =
(select distinct Items + ',' from #tScanRemake b WHERE a.ScanOrderCaseid= b.ScanOrderCaseid FOR XML PATH( '') )
FROM #tScanRemake a

UPDATE a
SET a. IsBridge = 1  Select Count (*) from (Select * From #tScanRemake b WHERE CHARINDEX ('Bridge', ItemsList)> 0 )
FROM #tScanRemake a

The 1st portion works great and gives me a ,separated list in the ItemsList field. What I want to do is update a bit field based on a substring in the ItemsList field.

The issue is the substring can occur more than once in the list. I only care if it occurs at all. If the string is found then I want to set the IsBridge field to 1 otherwise set it to 0.

I can't figure out how to search within the list.

palacsint
  • 28,416
  • 10
  • 82
  • 109
CraigBob
  • 147
  • 1
  • 5
  • 15

1 Answers1

2

You can use CASE condition in your UPDATE statement

UPDATE a
SET a.IsBridge = CASE WHEN CHARINDEX ('Bridge', ItemsList)> 0 THEN 1 ELSE 0 END
FROM #tScanRemake a
Community
  • 1
  • 1
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
  • That was my 1st try, but it errored out when there was more than one occurrence of Bridge. Is there a way to specify just the 1st occurrence of the substring? – CraigBob Jan 28 '14 at 05:56
  • CHARINDEX() returns the starting position of first occurenece of string to be searched... having more than one occurence has no effect.. – Mudassir Hasan Jan 28 '14 at 06:09
  • That worked. The error was from another update statement further in the proc. – CraigBob Jan 28 '14 at 16:41