update problem

[david12359]

我想更新我的表

FunctionLog
caseid functionid systemtype eventid
1 1 23
2 1 23
3 1 12
4 1 4

SystemEvent
eventid systemtype
4 1
12 3
23 4

如何用UPDATE语句来更新Functionlog里的systemtype里的值

UPDATE CVCT.dbo.FunctionLog SET systemtype =(Select distinct systemtype from FMCV.dbo.SystemEvent se where se.eventid in
(Select s.eventid from CVCT.dbo.FunctionLog s))

我的SQL语句不行， 得到的systemtype全是eventid23的值4


谁能帮我一下。

[paoluo]

Update A Set systemtype=B.systemtype
From FunctionLog A
Inner Join SystemEvent B
On A.eventid=B.eventid

[paoluo]

Update A Set systemtype=B.systemtype
From FunctionLog A,SystemEvent B
Where A.eventid=B.eventid

[paoluo]

--方法一
Update A Set systemtype=B.systemtype
From CVCT.dbo.FunctionLog A
Inner Join FMCV.dbo.SystemEvent B
On A.eventid=B.eventid
--方法二
Update A Set systemtype=B.systemtype
From CVCT.dbo.FunctionLog A,FMCV.dbo.SystemEvent B
Where A.eventid=B.eventid
--方法三
Update A
Set systemtype=(Select Distinct systemtype From FMCV.dbo.SystemEvent Where eventid=A.eventid) From CVCT.dbo.FunctionLog A

[david12359]

paoluo,我试了你的方法。和我一开始一样
得到的是
caseid functionid systemtype eventid
1 1 4 23
2 1 4 23
3 1 4 12
4 1 4 4

但我想要的是：
caseid functionid systemtype eventid
1 1 4 23
2 1 4 23
3 1 3 12
4 1 1 4

[paoluo]

不可能撒，你的語句貼出來看看。


Create Table FunctionLog
(caseid Int,
functionid Int,
systemtype Int,
eventid Int)

Create Table SystemEvent
(eventid Int,
systemtype Int)

Insert FunctionLog Select 1, 1,Null, 23
Union All Select 2, 1,Null, 23
Union All Select 3, 1,Null, 12
Union All Select 4, 1,Null, 4

Insert SystemEvent Select 4, 1
Union All Select 12, 3
Union All Select 23, 4
GO

--方法一
Update A Set systemtype=B.systemtype
From FunctionLog A
Inner Join SystemEvent B
On A.eventid=B.eventid

Select * From FunctionLog
GO
Drop Table FunctionLog,SystemEvent
--Result
/*
caseid functionid systemtype eventid
1 1 4 23
2 1 4 23
3 1 3 12
4 1 1 4
*/

[paoluo]

Create Table FunctionLog
(caseid Int,
functionid Int,
systemtype Int,
eventid Int)

Create Table SystemEvent
(eventid Int,
systemtype Int)

Insert FunctionLog Select 1, 1,Null, 23
Union All Select 2, 1,Null, 23
Union All Select 3, 1,Null, 12
Union All Select 4, 1,Null, 4

Insert SystemEvent Select 4, 1
Union All Select 12, 3
Union All Select 23, 4
GO
--方法二
Update A Set systemtype=B.systemtype
From FunctionLog A,SystemEvent B
Where A.eventid=B.eventid

Select * From FunctionLog
GO
Drop Table FunctionLog,SystemEvent
--Result
/*
caseid functionid systemtype eventid
1 1 4 23
2 1 4 23
3 1 3 12
4 1 1 4
*/

[paoluo]

Create Table FunctionLog
(caseid Int,
functionid Int,
systemtype Int,
eventid Int)

Create Table SystemEvent
(eventid Int,
systemtype Int)

Insert FunctionLog Select 1, 1,Null, 23
Union All Select 2, 1,Null, 23
Union All Select 3, 1,Null, 12
Union All Select 4, 1,Null, 4

Insert SystemEvent Select 4, 1
Union All Select 12, 3
Union All Select 23, 4
GO
--方法三
Update A
Set systemtype=(Select Distinct systemtype From SystemEvent Where eventid=A.eventid) From FunctionLog A

Select * From FunctionLog
GO
Drop Table FunctionLog,SystemEvent
--Result
/*
caseid functionid systemtype eventid
1 1 4 23
2 1 4 23
3 1 3 12
4 1 1 4
*/