一条高难度的update语句，求解

[windstore]

UPDATE T_UserLoginRoomLog SET
Area=B.Area,ModelType=B.ModelType,PlatForm = B.PlatForm,Lic = B.Lic
FROM (SELECT  TOP 1 * FROM T_UserLoginLog A WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime ORDER BY A.ID DESC) B 
WHERE T_UserLoginRoomLog.[Year]='2010' AND T_UserLoginRoomLog.[Month] ='1' AND T_UserLoginRoomLog.[Day]='28'

这样的语法为什么有问题捏，如果我想实现这样的效果，该怎么实现的。

要求：每次更新T_UserLoginRoomLog 的一条记录时，去T_UserLoginLog表找到一条记录，见上面的子查询，其中子查询需要用到T_UserLoginRoomLog中当前条记录的字段。

[bancxc]

帮顶 不太懂

[jwdream2008]

直接描述你的需求吧！这样看太麻烦啊！

[丰云]

[Quote=引用楼主 windstore 的回复:]
SQL codeUPDATE T_UserLoginRoomLogSET
Area=B.Area,ModelType=B.ModelType,PlatForm= B.PlatForm,Lic= B.LicFROM (SELECTTOP1*FROM T_UserLoginLog AWHERE A.UserID=UserLoginRoomLog.PerIDAND A.LoginTime<UserLoginRoomLog.RoomTimeORDERBY A.IDDESC) BWHERE T_UserLoginRoomLog.[Year]='2010'AND T_UserLoginRoomLog.[Month]='1'AND T_UserLoginRoomLog.[Day]='28'

这样的语法为什么有问题捏，如果我想实现这样的效果，该怎么实现的。

要求：每次更新T_UserLoginRoomLog 的一条记录时，去T_UserLoginLog表找到一条记录，见上面的子查询，其中子查询需要用到T_UserLoginRoomLog中当前条记录的字段。
[/Quote]
你这个语句问题非常大。。。。
update 和 where 之间居然直接有from 语句，真是有才呀。。。。

[tgbd]

一般的诸如用一个表的字段更新另一个表的字段，可以采用如下方法：
updata Table1 set Table1.F1=Table2.F1 from Table1,Table2
where Table1.F2=Table2.F2

[让你望见影子的墙]

where后面应该有B和你要更新表的链接条件

[cxmcxm]

--直接将(select top 1 * ...)的语句取值给set中的值赋值即可,还有select top 1 ...的查询是否一定有结果,如果无
  --结果,将是null,以下假设只给select top 1 * 查询有结果的记录update
 UPDATE T_UserLoginRoomLog SET
	Area=(SELECT  TOP 1 area FROM T_UserLoginLog A 
	  WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime 
	  ORDER BY A.ID DESC),
	ModelType=(SELECT  TOP 1 modeltype FROM T_UserLoginLog A 
	  WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime 
	  ORDER BY A.ID DESC),
	PlatForm = (SELECT  TOP 1 platform FROM T_UserLoginLog A 
	  WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime 
	  ORDER BY A.ID DESC),
	Lic = (SELECT  TOP 1 lic FROM T_UserLoginLog A 
	  WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime 
	  ORDER BY A.ID DESC)
WHERE T_UserLoginRoomLog.[Year]='2010' AND T_UserLoginRoomLog.[Month] ='1' AND T_UserLoginRoomLog.[Day]='28'
     and exists(SELECT * FROM T_UserLoginLog A 
	  WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime)
	  

[windstore]

谢谢大家~

[丰云]

UPDATE T_UserLoginRoomLog
SET
T_UserLoginRoomLog.Lic = B.Lic,
T_UserLoginRoomLog.Area = B.Area,
T_UserLoginRoomLog.ModelType = B.ModelType,
T_UserLoginRoomLog.PlatForm = B.PlatForm
FROM T_UserLoginRoomLog,T_UserLoginLog
WHERE T_UserLoginRoomLog.[Year] = '2010'
AND T_UserLoginRoomLog.[Month] = '1'
AND T_UserLoginRoomLog.[Day] = '28'
AND T_UserLoginLog.UserID = UserLoginRoomLog.PerID
AND T_UserLoginLog.LoginTime = UserLoginRoomLog.RoomTime
.
.
.
如果这样不能保证UserLoginLog表里的数据是唯一的，那就继续追加条件,直到唯一为止

[hughie]

UPDATE T_UserLoginRoomLog SET
Area=B.Area,
ModelType=B.ModelType,
PlatForm = B.PlatForm,
Lic = B.Lic
FROM T_UserLoginRoomLog A
JOIN ( SELECT Room.PerID, MAX(Log.ID) ID
FROM T_UserLoginLog Log INNER JOIN T_UserLoginRoomLog Room
WHERE Log.UserID=Room.PerID AND Log.LoginTime<Room.RoomTime
GROUP BY Room.PerID ) C ON A.PerID = C.PerID
JOIN T_UserLoginLog B ON C.ID=B.ID

[hughie]

不好意思，忘了你的条件了
UPDATE T_UserLoginRoomLog SET
Area=B.Area,
ModelType=B.ModelType,
PlatForm = B.PlatForm,
Lic = B.Lic
FROM T_UserLoginRoomLog A
JOIN ( SELECT Room.PerID, MAX(Log.ID) ID
FROM T_UserLoginLog Log INNER JOIN T_UserLoginRoomLog Room
WHERE Log.UserID=Room.PerID AND Log.LoginTime<Room.RoomTime
GROUP BY Room.PerID ) C ON A.PerID = C.PerID
JOIN T_UserLoginLog B ON C.ID=B.ID

WHERE A.[Year]='2010' AND A.[Month] ='1' AND A.[Day]='28'

[hughie]

其实，你只要把update表名 换为 select，相应的等号以及前边部分去点，执行一下，如果结果集正确，那么就可以更新成功。

[DBFocus]

可以分成两个语句写，这样逻辑比较清晰，下面写了一种，你可以试试。

select
	RoomLog.PerID,
	max(LoginLog.ID) maxID
into
	#NeedUpdate
from
	T_UserLoginRoomLog RoomLog
	inner join
	T_UserLoginLog LoginLog
	on
		RoomLog.PerID = LoginLog.UserID
		and
		RoomLog.RoomTime > LoginLog.LoginTime
where
	RoomLog.[Year] = '2010'
	and
	RoomLog.[Month] = '1'
	and
	RoomLog.[Day] = '28';

update
	RoomLog
set
	RoomLog.Area = LoginLog.Area,
	RoomLog.ModelType = LoginLog.ModelType,
	RoomLog.PlatForm = LoginLog.PlatForm,
	RoomLog.Lic = LoginLog.Lic
from
	T_UserLoginRoomLog RoomLog
	inner join
	#NeedUpdate NeedUpdate
	on
		RoomLog.PerID = NeedUpdate.PerID
	inner join
	T_UserLoginLog LoginLog
	on
		NeedUpdate.maxID = LoginLog.ID;

drop table #NeedUpdate;