if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A
(
FID varchar(1),
FCount int
)
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
create table #B
(
FID varchar(1),
FCount int
)
go
insert into #A(FID,FCount) values(1,0)
insert into #B(FID,FCount) values(1,10)
insert into #B(FID,FCount) values(1,20)
go
update t1
set t1.FCount=t2.FCount
from #A t1
left join #B t2 on t1.FID=t2.FID
go
select * from #A
go
if object_id('tempdb.dbo.#A') is not null drop table #A
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go 在SQL 2000中的结果是FID FCount
---- -----------
1 20(所影响的行数为 1 行)在SQL 2005中得到的结果是FID FCount
---- -----------
1 10(1 行受影响)
解决方案 »
- Debug错误问题材
- 前网管太阴险,把sa密码改了,客户端也改成5人访问,而且身份验证模式也改掉了,急救!!!!
- 在SqlServer2000的存储过程中"Alter table_name Modify column_name date_type(..)" 语句怎么写??
- ■如何取得某字符串前面几个字节?■
- 一个关于字符串查询的问题
- 很菜的问题:怎么判断数据库是否有记录?(着急,给分)
- 问sql2005一个ssis项目中的dtsx如何定时执行的问题
- 送分题sql server的 dump database master to "tape_device"备份系统表
- 如何建立E-R模型
- sql语句的问题。
- SQL 题目
- 求一个约束的写法
go
create table #A
(
FID varchar(1),
FCount int
)
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
create table #B
(
FID varchar(1),
FCount int
)
go
insert into #A(FID,FCount) values(1,0)
insert into #B(FID,FCount) values(1,10)
insert into #B(FID,FCount) values(1,20)
go
update t1
set t1.FCount=t2.FCount
from #A t1
left join #B t2 on t1.FID=t2.FID
go
select * from #A
go
if object_id('tempdb.dbo.#A') is not null drop table #A
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)FID FCount
---- -----------
1 20(所影响的行数为 1 行)测试结果,照理来说应该是20的,赋值被赋值了两次,得到的是最后一次赋值的结果
go
create table #A
(
FID varchar(1),
FCount int
)
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
create table #B
(
FID varchar(1),
FCount int
)
go
insert into #A(FID,FCount) values(1,0)
insert into #B(FID,FCount) values(1,10)
insert into #B(FID,FCount) values(1,20)
go
update t1
set t1.FCount=t2.FCount
from #A t1
left join #B t2 on t1.FID=t2.FID
go
select * from #A
go
if object_id('tempdb.dbo.#A') is not null drop table #A
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
/*FID FCount
---- -----------
1 10(1 行受影响)
*/
GO2)结果Rows Executes StmtText
1 1 update t1 set t1.FCount=t2.FCount from #A t1 left join #B t2 on t1.FID=t2.FID
1 1 |--Table Update(OBJECT:([tempdb].[dbo].[#A]), SET:([tempdb].[dbo].[#A].[FCount] = [tempdb].[dbo].[#B].[FCount] as [t2].[FCount]))
1 1 |--Top(ROWCOUNT est 0)
1 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[FID]))
1 1 |--Table Scan(OBJECT:([tempdb].[dbo].[#A] AS [t1]))
1 1 |--Stream Aggregate(DEFINE:([t2].[FCount]=ANY([tempdb].[dbo].[#B].[FCount] as [t2].[FCount])))
2 1 |--Table Scan(OBJECT:([tempdb].[dbo].[#B] AS [t2]), WHERE:([tempdb].[dbo].[#A].[FID] as [t1].[FID]=[tempdb].[dbo].[#B].[FID] as [t2].[FID]))3)取#A和#B LEFT JOIN匹配上的#B第一条(重复),你可以追加几条数据到#B,同时追加#A 再测结果. Rows Executes StmtText
-- 1 1 |--Stream Aggregate(DEFINE:([t2].[FCount]=ANY([tempdb].[dbo].[#B].[FCount] as [t2].[FCount])))
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A
(
FID varchar(1),
FCount int
)
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
create table #B
(
FID varchar(1),
FCount int
)
go
insert into #A(FID,FCount) values(1,0)
insert into #B(FID,FCount) values(1,10)
insert into #B(FID,FCount) values(1,20)
go select * from #A
select * from #bupdate t1
set t1.FCount=t2.FCount
from #A t1
left join #B t2 on t1.FID=t2.FID
go
select * from #A
go
if object_id('tempdb.dbo.#A') is not null drop table #A
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
set t1.FCount=t2.FCount
from #A t1
left join #B t2 on t1.FID=t2.FID
你的a,b两表是一对多的结果,这个语句本身就不准确.
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A
(
FID varchar(1),
FCount int
)
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
create table #B
(
FID varchar(1),
FCount int
)
go
insert into #A(FID,FCount) values(1,0)
insert into #B(FID,FCount) values(1,10)
insert into #B(FID,FCount) values(1,20)
go
update t1 set t1.FCount=(select top 1 FCount from #b t2 where t2.fid = t1.fid order by FCount) from #A t1
select * from #A
/*
FID FCount
---- -----------
1 10(所影响的行数为 1 行)
*/update t1 set t1.FCount=(select top 1 FCount from #b t2 where t2.fid = t1.fid order by FCount desc) from #A t1
select * from #A
/*
FID FCount
---- -----------
1 20(所影响的行数为 1 行)
*/go
if object_id('tempdb.dbo.#A') is not null drop table #A
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go /*
FID FCount
---- -----------
1 10(所影响的行数为 1 行)
*/--sql 2005
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A
(
FID varchar(1),
FCount int
)
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go
create table #B
(
FID varchar(1),
FCount int
)
go
insert into #A(FID,FCount) values(1,0)
insert into #B(FID,FCount) values(1,10)
insert into #B(FID,FCount) values(1,20)
go
update t1 set t1.FCount=(select top 1 FCount from #b t2 where t2.fid = t1.fid order by FCount) from #A t1
select * from #A
/*
FID FCount
---- -----------
1 10(1 行受影响)
*/update t1 set t1.FCount=(select top 1 FCount from #b t2 where t2.fid = t1.fid order by FCount desc) from #A t1
select * from #A
/*
FID FCount
---- -----------
1 20(1 行受影响)
*/go
if object_id('tempdb.dbo.#A') is not null drop table #A
go
if object_id('tempdb.dbo.#B') is not null drop table #B
go /*
FID FCount
---- -----------
1 10(1 行受影响)
*/
反正最后的值是表的最后一天记录Update上去的
1.首先排序没有关系,2000和2005的排序方式我设置得都是一样的。
2.并不是随机修改,你在往B表中多加几条数据,2000永远更新后的结果是B表的符合条件的最后一条数据,2005则相反。
3.10楼所说的 “你的a,b两表是一对多的结果,这个语句本身就不准确.”,能否在从2000和2005原理中说说看。为什么会结果不一样?
在我们进行基于连接的UPDATE操作的时候 要注意一个非常掩蔽的问题:
当你修改一对多关系连接中的“多”端的表时(例子里面是#A表),更新操作具有不确定性.
楼主这个问题可以参看技术内幕系列 T-SQL查询 这本书445页 第八章数据修改部分
Description from MS SQL 2005 Book online0)Microsoft SQL Server 2005 →
Documentation and Tutorials →
SQL Server Books Online →
Index →
Type : UPDATE statement [SQL Server]1)DescriptionUsing UPDATE with the FROM ClauseThe results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic. For example, in the UPDATE statement in the following script, both rows in Table1 meet the qualifications of the FROM clause in the UPDATE statement; but it is undefined which row from Table1 is used to update the row in Table2.2)SampleUSE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
(ColA int NOT NULL, ColB decimal(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2
(ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0);
INSERT INTO dbo.Table1 VALUES(1, 20.0);
INSERT INTO dbo.Table2 VALUES(1, 0.0);
GO
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB
FROM dbo.Table2;
3)However testing results are Still the same for version 2005(1 row(s) affected)
ColA ColB
----------- ---------------------------------------
1 10.000(1 row(s) affected)
zantong
-----------------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
FID FCount
---- -----------
1 20Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)FID FCount
---- -----------
1 10Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
FID FCount
---- -----------
1 10
建议你连接用主键或者唯一键;