473,376 Members | 1,187 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,376 software developers and data experts.

Updating the same column multiple times in one update statement

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

Jul 23 '05 #1
8 11556
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)
Jul 23 '05 #2
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
Jul 23 '05 #3
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.

Jul 23 '05 #4
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

Jul 23 '05 #5
>> 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.

Jul 23 '05 #6
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
Jul 23 '05 #7
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)
Jul 23 '05 #8
Hmmm.... redeisgn or use cursors - Cursors it is then. Thanks for the
help guys.

Jul 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: mlrehberg | last post by:
Hi, New to writing sql script I get this error in my sql script Server: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery...
9
by: allenj | last post by:
DB2 UDB 7.2 WSE Fixpak 9 Linux Red Hat 7.3 I have some library code (written in Java, if that matters) that processes maintenance screens that are presented to the end-users as forms in a...
4
by: Robert Stearns | last post by:
I loaded a flat file into an SQL table and verified the data. Now I want to move the data from the input table to the real table. I thought to use a SQL statement like: update is2.animals t1...
3
by: Tc | last post by:
Hi, I was curious, I am thinking of writing an application that loads a dataset from a database that resides on a server. The question I have is this, if multiple copies of the app will be...
0
by: cwbp17 | last post by:
I'm having trouble updating individual datagrid cells. Have two tables car_master (columns include Car_ID, YEAR,VEHICLE) and car_detail (columns include Car_ID,PRICE,MILEAGE,and BODY);both tables...
14
by: el_sid | last post by:
Our developers have experienced a problem with updating Web References in Visual Studio.NET 2003. Normally, when a web service class (.asmx) is created, updating the Web Reference will...
6
by: Dave | last post by:
I want to put the information that the user selects in my combo boxes into a subform that lies on the same form as the combo boxes. Thanks for your help already, Dave
6
by: sumithar | last post by:
I have a php application which has a page where some personal details can be updated. One of the details is a photo. The backend is mysql. My question has to do with how best to code for this. ...
6
by: Al Moodie | last post by:
Hi, I have a MySQL database where I want to updated multiple records. The table has two columns: product_number product_price I have a list with first entry product_price, second entry...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.