May 16, 2008 at 2:39 am
Does anyone know if the T-SQL UPDATE..FROM query type has changed between the 2000 and 2005 versions?
I have always used the following type of syntax for updating a table from another one based on a common field:
UPDATE Table1
SET Field2 = b.Field2
FROM Table1 a, Table2 b
WHERE a.Field1 = b.Field1
This would normally set the value of Field2 in Table1, to be the same as that in Table2, where the values of Field1 match.
However, after running exactly the same script in SQL Server 2005, instead of this happening, the value of Field2 was set to the same value for every single row! It's almost as if it has ignored the WHERE clause.
Can anyone shed any light on why this statement is now processed differently? Is this a brand new version of T-SQL? And is UPDATE..FROM not safe to use anymore?
Any help greatly appreciated...
May 16, 2008 at 3:07 am
I have tried the same and its working properly.
Check the following code.
create table table1(field1 int, field2 varchar(20))
create table table2(field1 int, field2 varchar(20))
insert into table1 values(1,'abc')
insert into table2 values(1,'def')
select * from table1
field1 field2
----------- --------------------
1 abc
select * from table2
field1 field2
----------- --------------------
1 def
execute the update statement
update table1 set field2 = b.field2
from table1 a, table2 b
where a.field1 = b.field1
select * from table1
field1 field2
----------- --------------------
1 def
select * from table2
field1 field2
----------- --------------------
1 def
May 16, 2008 at 5:07 am
Hi Emma,
Were you on the SSIS course in London a couple of months ago?
that aside I have had a little look at this and think that the problem is uniquely identifying the rows.
I ran the following code;
create table table1(field1 int, field2 varchar(20))
create table table2(field1 int, field2 varchar(20))
insert into table1 values(1,'abc')
insert into table2 values(1,'a')
insert into table1 values(1,'abc')
insert into table2 values(1,'b')
insert into table1 values(1,'abc')
insert into table2 values(1,'c')
insert into table1 values(1,'abc')
insert into table2 values(1,'d')
select * from table1
select * from table2
results;
table1
field1 field2
1abc
1abc
1abc
1abc
table2
field1 field2
1a
1b
1c
1d
Then ran the update
update table1 set field2 = b.field2
from table1 a, table2 b
where a.field1 = b.field1
select * from table1
select * from table2
results;
table1
field1 field2
1a
1a
1a
1a
table2
field1 field2
1a
1b
1c
1d
Then I changed field 1 so it could identify the rows properly, with the following code.
create table table1(field1 int, field2 varchar(20))
create table table2(field1 int, field2 varchar(20))
insert into table1 values(1,'abc')
insert into table2 values(1,'a')
insert into table1 values(2,'abc')
insert into table2 values(2,'b')
insert into table1 values(3,'abc')
insert into table2 values(3,'c')
insert into table1 values(4,'abc')
insert into table2 values(4,'d')
select * from table1
select * from table2
results;
table1
field1 field2
1abc
2abc
3abc
4abc
table2
field1 field2
1a
2b
3c
4d
Then ran the update
update table1 set field2 = b.field2
from table1 a, table2 b
where a.field1 = b.field1
select * from table1
select * from table2
results;
table1
field1 field2
1a
2b
3c
4d
table2
field1 field2
1a
2b
3c
4d
and it seems to do what you want!
Hope this helps! Wonder if I got some work to do yet 😉
May 16, 2008 at 7:06 am
No but I did do the course in Leeds!!
Thanks for the replies both, I'll check them out
May 16, 2008 at 8:06 am
Use it all the time, but like this:
UPDATE Table1
SET Field2 = b.Field2
FROM Table1 a
JOIN Table2 b
ON a.Field1 = b.Field1
I don't know that the ANSI standard on the join would make a difference, but it doesn't seem to hurt.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2008 at 8:28 am
Instead of "Update Table1", use the alias you use in the From clause. That's how I do it (otherwise, it doesn't know which instance of Table1 you want to update, a or b).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply