我要更新这个EmployeesAttendance 表AbsenteeismHours 字段值为5,条件是
PublicRest表里面Sunday字段为1时,EmployeesAttendance表DateMonths星期六星期天时就不更新.其它的更新
如果PublicRest没有数据时就全更新.
我写成下面这样PublicRest表没有数据.就全不更新了,不知为什么UPDATE EmployeesAttendance SET AbsenteeismHours = 5FROM EmployeesAttendance,PublicRest
WHERE NOT EXISTS(SELECT 1 FROM PublicRest WHERE EmployeesAttendance.PostID=PublicRest.PostID AND
DATEPART(dw,DateMonths) IN (1,7)and PublicRest.Sunday = 1)
PublicRest表里面Sunday字段为1时,EmployeesAttendance表DateMonths星期六星期天时就不更新.其它的更新
如果PublicRest没有数据时就全更新.
我写成下面这样PublicRest表没有数据.就全不更新了,不知为什么UPDATE EmployeesAttendance SET AbsenteeismHours = 5FROM EmployeesAttendance,PublicRest
WHERE NOT EXISTS(SELECT 1 FROM PublicRest WHERE EmployeesAttendance.PostID=PublicRest.PostID AND
DATEPART(dw,DateMonths) IN (1,7)and PublicRest.Sunday = 1)
解决方案 »
- SQL 2008问题请教
- 求一句SQL语句
- 【求助】关于sql server 2005的两个问题
- 如何得到这样的查询结果?
- 如何获取当前数据库下的所有表
- 如何用sql 语句取出发布复制中以发布的所有<发布的名称>
- CSDN 个人空间,有时爆错。MySQL Error Message: Can not connect to MySQL server。。
- 如何得到如下结果,求一sql语句,或者存储过程,
- 请问怎样在存储过程里根据条件选择不同的数据库,就是有一个变量放数据库的名称??
- 紧急求助!请较一个SQL语句,关于count()
- 请教:sqlserver执行update的时候只显示命令已成功完成,而不是显示影响行数
- mysql存储过程问题(急...)
UPDATE EmployeesAttendance
SET AbsenteeismHours = 5
FROM PublicRest
where EmployeesAttendance.PostID=PublicRest.PostID
and DATEPART(weekday,DateMonths) not IN (1,7)
and PublicRest.Sunday = 1
UPDATE EmployeesAttendance SET AbsenteeismHours = 5
FROM EmployeesAttendance
WHERE NOT EXISTS(SELECT 1 FROM PublicRest WHERE EmployeesAttendance.PostID=PublicRest.PostID AND
DATEPART(dw,DateMonths) IN (1,7)and PublicRest.Sunday = 1)
请去掉PublicRest这张表
不然无法更新的,去掉后能正常的
set a='a'
from #a,#b
你这样写的话
如果b表为空集的话,是无法更新a表的
WHERE NOT EXISTS(SELECT 1 * FROM PublicRest WHERE EmployeesAttendance.PostID=PublicRest.PostID AND
DATEPART(dw,DateMonths) IN (1,7)and PublicRest.Sunday = 1)
你直接select 1,那么跟没查有啥区别,加个*或者其他字段就应该OK了。试下。
UPDATE EmployeesAttendance SET AbsenteeismHours = 5
FROM EmployeesAttendance
--如果PublicRest没有数据时就全更新
where not exists (select 1 from PublicRest)
--PublicRest表里面Sunday字段为1时,EmployeesAttendance表DateMonths星期六星期天时就不更新
or exists (SELECT 1 FROM PublicRest WHERE EmployeesAttendance.PostID=PublicRest.PostID AND
DATEPART(dw,DateMonths) not IN (1,7)and PublicRest.Sunday = 1)