can this be done?

[snair]

It is possible to update a column 'C1' of table 'T' with values from the same column 'C1' of the same table 'T' but of a different row, with a single update statement.

For eg:

update T1
set T1.C1 = T2.C1
from T T1, T T2
where T1.C3 = 'TO'
and T2.C3 = 'FROM'

Here the rows where the column C3 has a value 'FROM' will also have the column C1 poulated. I have to get this value from C1 and update the C1 of different row where the C3 will have a value 'TO'

Is it possible in a single update statement without using corelated subqueries (just using the joins).

Thank you
Suresh

[cs_troyk]

snair;

UPDATE ... FROM is non-ANSI, but here's the solution. Why, exactly, do you need to do this like this?

-------------- SCRIPT -----------
CREATE TABLE T(c1 int, c2 varchar(4))
GO

SET NOCOUNT ON
INSERT INTO T VALUES(1, 'TO')
INSERT INTO T VALUES(2, 'FROM')
SET NOCOUNT OFF
GO

SELECT * FROM T
GO

UPDATE t1
SET t1.c1 = t2.c1
FROM T t1
INNER JOIN T t2
ON 1 = 1 --Replace w/ some "pairing" clause?
WHERE t1.c2 = 'TO'
AND t2.c2 = 'FROM'
GO

SELECT * FROM T
GO

DROP TABLE T
--------------- END SCRIPT -----------

HTH,
TroyK, MCSD

[snair]

Hi troyk,

Thank you very much.

This was needed to update the old records in a transaction table (which also has the new set of data) with the values from the new data for some processing.

What I was missing was using the Alias directly in the Update statement.

I was doing something like below:

UPDATE T
SET c1 = t2.c1
FROM T t2
where ON 1 = 1 --Replace w/ some "pairing" clause?
WHERE c2 = 'TO'
AND t2.c2 = 'FROM'


Thank you very much once again.

Suresh