想要用一个替换几个更新 [英] want to replace several updates with just one

问题描述

嗨所有



i有一个gridview我在哪里显示时间表



第一列是iduser以下是从一个月的第1天开始的日子



我想要的是在时间表中设置假日天数



这里是我的sql代码

hi to all

i have a gridview where i show a schedule

first column is the iduser the following are the days, starting at day 1 of a month

what i want is to set the holidays days in the schedule

here is the sql code that i have

DECLARE @Date_Start AS DATETIME 
DECLARE @Schedule TABLE(Idx NUMERIC IDENTITY(1,1) PRIMARY KEY, IdUser NVARCHAR(20), D01 CHAR(14), D02 CHAR(14), D03 CHAR(14), D04 CHAR(14), D05 CHAR(14), UNIQUE(IdUser)) 
DECLARE @Holidays TABLE(Idx NUMERIC IDENTITY(1,1) PRIMARY KEY NOT NULL, Holiday DATETIME NOT NULL, UNIQUE(Holiday)) 

SET @Date_Start = CAST('2014-01-01 00:00:00.000' AS DATETIME) 

INSERT INTO @Schedule VALUES('0708', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5') 
INSERT INTO @Schedule VALUES('0899', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5') 
INSERT INTO @Schedule VALUES('0933', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5') 
INSERT INTO @Schedule VALUES('1097', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5') 
INSERT INTO @Schedule VALUES('1126', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5') 
INSERT INTO @Schedule VALUES('1513', 'day 1', 'day 2', 'day 3', 'day 4', 'day 5') 

INSERT INTO @Holidays VALUES(CAST('2014-01-02 00:00:00.000' AS DATETIME)) 
INSERT INTO @Holidays VALUES(CAST('2014-01-04 00:00:00.000' AS DATETIME)) 


--UPDATE X SET X.[D01] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '0' 
--UPDATE X SET X.[D02] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '1' 
--UPDATE X SET X.[D03] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '2' 
--UPDATE X SET X.[D04] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '3' 
--UPDATE X SET X.[D05] = 'Holiday' FROM @Schedule X, @Holidays Y WHERE DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '4' 


UPDATE X 
SET [D01] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '0' THEN 'Holiday' ELSE [D01] END, 
[D02] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '1' THEN 'Holiday' ELSE [D02] END, 
[D03] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '2' THEN 'Holiday' ELSE [D03] END, 
[D04] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '3' THEN 'Holiday' ELSE [D04] END, 
[D05] = CASE WHEN DATEDIFF(dd, @Date_Start, Y.[Holiday]) = '4' THEN 'Holiday' ELSE [D05] END 
FROM @Schedule X INNER JOIN @Holidays Y ON Y.Idx IN ('1', '2') 

SELECT * FROM @Schedule 
SELECT * FROM @Holidays 

评论的更新做我的意思想要



非评论是我网络搜索的结果，但我不明白这种更新格式



ON部分是我最后一次尝试了解它是如何工作的经历...但我没有得到它



对此有所帮助好的或不同的方式



感谢您的时间和回复

the commented updates do what i want

the non commented is the result of my web search, but i don't understand this format of update

the "ON" part is my last experience trying to understand how it works... but i don't get it

some help on this would be nice or a diffrent way

thanks for the time and replys

推荐答案

它看起来我是卡住

内容很短？！...

it looks that i am stuck
the content is short?!...

这篇关于想要用一个替换几个更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！