如何写这个SQL语句更新在哪里？
问题描述：
Update [E1$]
set [LR/Virtual/MW]='LR' ,
[Vir No#]=null
where [Conc] , [Vir No#]
in (
SELECT [Conc] , [Vir No#]
FROM [E1$]
where [Vir No#] is not null
group by Conc , [Vir No#]
having Count(LR)<28
)
答
尝试：
Update E set [LR/Virtual/MW]='LR' , [Vir No#]=null
from [E1$] E join
(SELECT [Conc] , [Vir No#]
FROM [E1$] where [Vir No#] is not null
group by Conc ,[Vir No#]
having Count(LR)<28
) a
on e.[Conc]=a.[Conc]
and e.[Vir No#]=a.[Vir No#]
答
尝试在where子句中使用围绕([conc], [Vir No#])的括号。
Update [E1$]
set [LR/Virtual/MW]='LR' ,
[Vir No#]=null
where ([Conc] , [Vir No#]) in
(
SELECT [Conc] , [Vir No#]
FROM [E1$]
where [Vir No#] is not null
group by Conc , [Vir No#]
having Count(LR)<28
)
答
Update [E1$]
set [LR/Virtual/MW]='LR',[Vir No#]=null
where exists(
SELECT [Conc] , [Vir No#]
FROM [E1$] e2
where [Vir No#] is not null and [E1$].[Conc]=e2.[Conc] and [E1$].[Vir No#]=e2.[Vir No#]
group by Conc , [Vir No#]
having Count(LR)<28
)
我收到异常> System.Data.OleDb.OleDbException（0x80040E14）：语法错误从[E1（缺少运算符）查询表达式“空$] E join（SELECT [Conc]，[Vir No＃] FROM [E1 $]其中[Vir No＃]不是空组by Conc，[Vir No＃]有Count（LR） 2012-08-08 13:38:53
@AhmedDarwish：您是否获得在SQL服务器或前端应用程序中的错误？ – 2012-08-08 13:40:55
前端......当我在SQL管理工作室中运行它时，它正常运行 – 2012-08-08 13:43:48