|zyciis| 求一条表字段根据另一个表更新语句

[zyciis292]

update Employee set
CompanyID = (select top 1 ID from Company where Name = Employee.CompanyName)
and CompanyID is null
-----------------------
我要更新员工表的CompanyID字段那第一条公司表中和当前员工表中公司名称一致的ID
以上是我写的语句，但在Oracle中的PL/SQL不通过
改为
update Employee set
CompanyID = (select ID from Company where Name = Employee.CompanyName)
and CompanyID is null
可以
那少了top 1的话SQL的意思还是和我上面的一样吗？
还是要怎么写

谢谢

[qydvip]

update Employee set
CompanyID in (select top 1 ID from Company where Name = Employee.CompanyName)
and CompanyID is null

试试

[Ni_Yang]

应该不一样吧。如果你ID 是独一无二的。就差不多。如果你根据ID可以查出2条数据的话那么有top 就只会根据你第一个数据。。如果是ID 只能查出一条。那么就是一样的。

[zyciis292]

楼上的，如果不一样的话，那这条更新要怎么写啊。。top 1的语法验证不过去

[zyciis292]

UP...........

[Lisliefor]

update Employee set CompanyID=Company.CompanyName
from Company
where Employee.CompanyName=Company.Name and Employee.CompanyID is null

[yibey]

update Employee set 
CompanyID = (select top 1 ID from Company where Name = Employee.CompanyName)
where  CompanyID is null

[lyy_0814]

楼主的Sql只有Top那里有问题吗？
“ CompanyID is null”是一个条件，这里用的是“and”

[zyciis292]

update t_p_ds set
dwbh = (select top 1 dwbh from t_p_dw
where nsrbm = t_p_ds.col002 and zzjgdm = replace(t_p_ds.col004,'-','')
) where dwbh is null
----------
这是我的源SQL，但提示:
ORA-00923: FROM keyword not found where expected

[暖枫无敌]

Oracle中没有top语法，所以你的写法是错误的，TOP对应oracle中的保留字是ROWNUM

update Employee set
CompanyID = (select ID from Company where ROWNUM = 1 and Name = Employee.CompanyName order by ID desc)
and CompanyID is null

[jyh070207]

[Quote=引用 9 楼 taomanman 的回复:]
Oracle中没有top语法，所以你的写法是错误的，TOP对应oracle中的保留字是ROWNUM

update Employee set
CompanyID = (select ID from Company where ROWNUM = 1 and Name = Employee.CompanyName order by ID desc)
and CompanyID is nu……
[/Quote]
Oracle中没有top语法