sql server programming: Update row from values in another row in same table -- sql server programming

Re: Update row from values in another row in same table

xMan
10/13/2007 8:30:01 PM

I really appreciate your help on this!!!!

However I get the following error when I paste this into EM:

Error in table name or view name in UPDATE clause.
Unable to parse query text.

I then remove the T1 from line 1

and get:

Then it shows:
Incorrect syntax error near 'T1'

then:

Cannot use the column prefix 'T1' ....

any other ideas?
Thanks again for your help. xMan

[quoted text, click to view]

Re: Update row from values in another row in same table

Awlnoing
10/13/2007 9:42:01 PM

If you drop the t1. from the SET (or add the t1 alias to the UPDATE ) it
should work.

UPDATE tblTest t1
SET t1.Field1 = t2.Field1
FROM tblTest AS t1 INNER JOIN
tblTest AS t2 ON t1.ID = t2.ID
WHERE t1.Rev = 97 AND t2.Rev = 99

or

UPDATE tblTest
SET Field1 = t2.Field1
FROM tblTest AS t1 INNER JOIN
tblTest AS t2 ON t1.ID = t2.ID
WHERE t1.Rev = 97 AND t2.Rev = 99

[quoted text, click to view]

Re: Update row from values in another row in same table

Uri Dimant
10/14/2007 12:00:00 AM

Hi
Untested

UPDATE tblTest SET Field1=(
SELECT TOP 1 Field1 FROM tblTest WHERE ID=2 AND Rev<(SELECT MAX(Rev)FROM
tblTest)
ORDER BY Rev DESC)
WHERE Rev=(SELECT MAX(Rev)FROM tblTest)
AND ID=(SELECT MAX(ID)FROM tblTest WHERE Rev=(SELECT MAX(Rev)FROM tblTest))

[quoted text, click to view]

Re: Update row from values in another row in same table

David Portas
10/14/2007 12:00:00 AM

[quoted text, click to view]

Don't use EM for testing code. Use Query Analyzer. I think you'll need to
make some changes though because both his suggestions cause errors when I
tried them. Try this:

CREATE TABLE [tblTest] (
[Rev] [int] NOT NULL ,
[ID] [int] NOT NULL ,
[Field1] [int] NOT NULL ,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[Rev],
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO tblTest (Rev, ID, Field1) VALUES (97,1,1);
INSERT INTO tblTest (Rev, ID, Field1) VALUES (99,1,2);

UPDATE t1
SET t1.Field1 = t2.Field1
FROM tblTest AS t1
JOIN tblTest AS t2
ON t1.ID = t2.ID
WHERE t1.Rev = 97 AND t2.Rev = 99;

SELECT * FROM tblTest;

Result:

Rev ID Field1
----------- ----------- -----------
97 1 2
99 1 2

(2 row(s) affected)

--
David Portas

Re: Update row from values in another row in same table

xMan
10/14/2007 10:16:00 AM

That worked perfectly. Wonderful. Thank-You!!!!

I rephrased the Update to:

UPDATE tblTest
SET Field1=(SELECT Field1 FROM tblTest WHERE ID=2 AND Rev=97)
SET Field2=(SELECT Field2 FROM tblTest WHERE ID=2 AND Rev=97)
.
.
WHERE Rev=(99) AND ID=(2)

Is this the simplest way of handleing multiple fields? Thanks again. xMan

[quoted text, click to view]

Re: Update row from values in another row in same table

xMan
10/15/2007 4:26:00 PM

Thanks to all,

That is exactly what I was looking for.

You guys are the best. xMan

[quoted text, click to view]

Re: Update row from values in another row in same table

Hugo Kornelis
10/15/2007 8:27:36 PM

[quoted text, click to view]

Hi xMan,

No, it's not. Unfortunately, apart from the fact that repeating the same
subquery over and over is clumsy, the optimizer also does a poor job of
optimizing these queries.

You'll get shorter and easier to maintain code plus faster execution if
you use the T-SQL proprietary version of the update command instead. But
do please beware of the issues this has if there's no perfect 1 on 1
match (Google for details).

UPDATE dest
SET column1 = src.column1,
column2 = src.column2,
...
FROM tblTest AS dest
INNER JOIN tblTest AS src
ON src.ID = dest.ID
AND src.Rev = 97
WHERE dest.ID = 2
AND dest.Rev = 99;

--
Hugo Kornelis, SQL Server MVP

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.