Updating the same column multiple times in one update statement

[pb648174]

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

[Hugo Kornelis]

On 15 Mar 2005 12:04:40 -0800, pb648174 wrote:

(snip)

Does anybody know of a switch or setting that can make this work
Hi pb,

There is none. An update first calculates ALL new data in a temp working
set (all based on existing data), then replaces all data "at once".
That's why you can switch column values without intermediate "helper"
column: "UPDATE MyTable SET ColA = ColB, ColB = ColA" will work.

Since SQL Server is free to choose it's execution plan, there is no way
to predict the order in which rows are evaluated. If the "new" values of
updated rows would be used to calculate the "new" values for other rows,
the outcome would depend on order of evaluation - and since that can
change from execution to execution, so would the results. Not good, eh?

, or do
I need to stick with the cursors?

Hopefully not.

I suggest you post some more information. You already have posted the
table structure, but a few illustrative rows of sample data (as INSERT
statements) and the expected output for that sample data would be great
as well. And possibly the existing cursor-based code, to clarify further
what exactly you're trying to do. And once we understand what you try to
do, we can also try to find set-based alternatives for you.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

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?

You included tables, but you did not include any sample data (should
be as INSERT statements) and result given the sample. Therefore I
have some difficulties to understand what you are trying achieve.

If the list has a known maximum length, you can achieve this with an
n-way join.

If the list can have any number of elements, there is no documented and
supported way to achieve this. You might be able to find an UPDATE
statement that appears to give you the correct result, but you would
be rely on undefined behaviour.

So, you would have to rely on cursors.

In SQL2005, the next version of SQL Server, this may be possible to
achieve with a single statement, although with a syntax that is non-
obvious. (You need to delve into XML functions.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[pb648174]

The insert data is fairly simple, i.e.

--identity insert on
Insert INTO ScheduleTask (Id, IdHierarchy, CopyTaskId) values
(1,'.1.',null)
Insert INTO ScheduleTask (Id, IdHierarchy, CopyTaskId) values
(2,'.1.2.',null)

this signifies that 2 is a child of 1

then the copy process happens, i.e.
Insert INTO ScheduleTask (Id, IdHierarchy, CopyTaskId) values
(3,'.1.',1)
Insert INTO ScheduleTask (Id, IdHierarchy, CopyTaskId) values
(4,'.1.2.',2)

and the idhierarchies must be converted via the process above.
Currently I just loop through each item where copytaskid <> null and do
an update, replacing '.' + CopyTaskId + '.' with actual Id for all
items where copytaskid is not null.

[pb648174]

Current cursor based code:
--update idhierarchy
declare @NewId int
declare @OldId int

--make cursor so we can convert all old fileobjectid refs to new ones
DECLARE Item_Cursor CURSOR LOCAL FAST_FORWARD FOR
Select Id,CopyTaskId From ScheduleTask
Where CopyTaskId IS NOT NULL

OPEN Item_Cursor
FETCH NEXT FROM Item_Cursor INTO @NewId,@OldId

WHILE @@FETCH_STATUS = 0
BEGIN
--update idhierarchies
Update ScheduleTask Set
ScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +
CAST(@OldId as varchar) + '.', '.' + CAST(@NewId as varchar) + '.')
Where CopyTaskId IS NOT NULL

FETCH NEXT FROM Item_Cursor INTO @NewId,@OldId
END
CLOSE Item_Cursor
DEALLOCATE Item_Cursor

[--CELKO--]

>> 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
<<

No, this is not a relational approach, so you will have to revert to
cursors. SQL is set-oriented and does the updating "all at once", not
one row at a time.

However, it looks like you are trying to model a hierarchy and there
are other ways to do that. I have a whole book on TREES & HIERARCHIES
IN SQL that might help.

Also, IDENTITY cannot ever be a key in a RDBMS by definition.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

The insert data is fairly simple, i.e.

--identity insert on
Insert INTO ScheduleTask (Id, IdHierarchy, CopyTaskId) values
(1,'.1.',null)
Insert INTO ScheduleTask (Id, IdHierarchy, CopyTaskId) values
(2,'.1.2.',null)

this signifies that 2 is a child of 1

then the copy process happens, i.e.
Insert INTO ScheduleTask (Id, IdHierarchy, CopyTaskId) values
(3,'.1.',1)
Insert INTO ScheduleTask (Id, IdHierarchy, CopyTaskId) values
(4,'.1.2.',2)

and the idhierarchies must be converted via the process above.

I think that I understand less now than what I did before. Please keep
in mind that while you understand the business rules for your problem
very well, we on the outside have very little knowledge. Therefore may
have to walk an extra mile to explain what is going on to get help.

Looking at your cursor, it may be that the cursor can be replaced,
but I think I need to see a broader sample. And most of all I need to
see the desired results.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On Wed, 16 Mar 2005 23:20:01 +0000 (UTC), Erland Sommarskog wrote:

(snip)

I think that I understand less now than what I did before. Please keep
in mind that while you understand the business rules for your problem
very well, we on the outside have very little knowledge. Therefore may
have to walk an extra mile to explain what is going on to get help.

Looking at your cursor, it may be that the cursor can be replaced,
but I think I need to see a broader sample. And most of all I need to
see the desired results.

Hi Erland,

I ran his cursor on the sample data to get the desired results. I think
I'm starting to understand his data now.

The starting two rows are

Id IdHierarchy
1 .1.
2 .1.2.

The Id identifies each row. The IdHierarchy identifies the place of the
row in a hierarchy, by including the complete hierarchy, seperated by
periods, and an extra period at the beginning and end for convenience.

For row 1, the hierarchy is .1., so it's at the top of the hierarchy.
For row 2, the hierarchy is .1.2., so row 1 is the parent of row 2.
If there were a row with hierarchy .1.2.6.34.76., then it would have id
76; row 34 wopuld be it's parent, row 6 it's grandparent and row 2 it's
grand-grandparent.

The new rows look like this

Id IdHierarchy CopyTaskId
3 .1. 1
4 .1.2. 2

The IdHierarchy data is wrong. That is because these rows were added as
a copy of the existing rows (probably a business need to add a new
hierarchy with the same structure as an existing hierarachy). To solve
this, the code in the cursur changes all '.1.' to '.3.' (because 3 is
the Id of the row that's created as a copy of 1) and all '.2.' to '.4.',
but only on the copied rows. The original rows (1 and 2, with CopyTaskId
NULL) are unchanged. The end result after running the cursor is:

Id IdHierarchy
3 .3.
4 .3.4.

Yesterday evening, I've been trying to find a way to do this replace
action set-based, but I didn't succeed. For that, T-SQL needs to have a
REPLACE function that takes sets as input...

Maybe there is a way after all. Maybe the approach should not be to
replace things in the existing string, but to build a new string,
somehow using the existing structure and the unsupported aggregated
concatenation - but it woud be a very ugly kludge, so I didn't
investigate it fuurther.

My guess is that the OP would be best advised to get Joe Celko's book on
trees and hierarchies in SQL and find a model that's better suited for
his needs.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[pb648174]

Hmmm.... redeisgn or use cursors - Cursors it is then. Thanks for the
help guys.