I have a single update statement that updates the same column multiple
times in the same update statement. Basically i have a column that
looks like .1.2.3.4. which are id references that need to be updated
when a group of items is copied. I can successfully do this with
cursors, but am experimenting with a way to do it with a single update
statement.
I have verified that each row being returned to the Update statement
(in an Update..From) is correct, but that after the first update to a
column, the next row that does an update to that same row/column combo
is not using the updated data from the first update to that column.
Does anybody know of a switch or setting that can make this work, or do
I need to stick with the cursors?
Schema detail:
if exists( select * from sysobjects where id = object_id(
'dbo.ScheduleTask') and type = 'U')
drop table dbo.ScheduleTask
go
create table dbo.ScheduleTask (
Id int not null identity(1,1),
IdHierarchy varchar(200) not null,
CopyTaskId int null,
constraint PK_ScheduleTask primary key nonclustered (Id)
)
go
Update query:
Update ScheduleTask Set
ScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +
CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +
CAST(TaskCopyData.Id as varchar) + '.')
From
ScheduleTask
INNER JOIN ScheduleTask as TaskCopyData ON
ScheduleTask.CopyTaskId IS NOT NULL AND
TaskCopyData.CopyTaskId IS NOT NULL AND
charindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',
ScheduleTask.IdHierarchy) > 0
Query used to verify that data going into update is correct:
select
ScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +
CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +
CAST(TaskCopyData.Id as varchar) + '.'
From
ScheduleTask
INNER JOIN ScheduleTask as TaskCopyData ON
ScheduleTask.CopyTaskId IS NOT NULL AND
TaskCopyData.CopyTaskId IS NOT NULL AND
charindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',
ScheduleTask.IdHierarchy) > 0
