/*
RiCo技術學院
微軟SQL Server 2022基礎資料庫設計和開發
   UPDATE單筆或多筆    
*/
--更新單筆單一欄位資料
  update [dbo].[AWBuildVersion] set VersionDate='2018-01-13 13:54:55.000'
  where [SystemInformationID]=1


--更新多筆多欄位資料
SELECT [DatabaseLogID]    
      ,[DatabaseUser]
      ,[Object]    
  FROM [dbo].[DatabaseLog]
  where [DatabaseLogID] between 1 and 10

  update [dbo].[DatabaseLog] set DatabaseUser=DatabaseUser+'_test', [Object]=[Object]+'_test'
  where [DatabaseLogID] between 1 and 10
  
  select @@ROWCOUNT --取得更新資料總筆數


--更新資料至預設值
GO
CREATE TABLE dbo.TestDefault  
(  
    c1 int identity(1,1),   
    c2 varchar(30)   
        CONSTRAINT default_name DEFAULT ('my column default') ,  
    c3 datetime2 not NULL DEFAULT  (getdate())
);  
GO    
INSERT INTO dbo.TestDefault (c2, c3)   
    VALUES ('rico', DEFAULT)
	,('rico1', DEFAULT)
	,('rico2', DEFAULT);  
	
update dbo.TestDefault set c2=DEFAULT
select @@ROWCOUNT --取得更新資料總筆數
GO  
SELECT * from dbo.TestDefault


--更新資料搭配TOP
SELECT TOP(10) * FROM HumanResources.Employee
 
UPDATE TOP(10) HumanResources.Employee
SET VacationHours = VacationHours * 1.05 ;


/*
更新資料搭配OUTPUT
*/
SELECT BusinessEntityID, VacationHours, ModifiedDate  
FROM HumanResources.Employee
WHERE VacationHours < 5

--宣告資料表變數 
DECLARE @MyTblVar TABLE (  
    BusinessEntityID int NOT NULL,  
    OldVacationHours smallint,  
    NewVacationHours smallint,  
    ModifiedDate datetime);  

--更新多筆多欄位	
UPDATE HumanResources.Employee  
SET VacationHours =  VacationHours * 2,  
    ModifiedDate = GETDATE()   
OUTPUT inserted.BusinessEntityID,  
      deleted.VacationHours,  
      inserted.VacationHours,  
      inserted.ModifiedDate  
INTO @MyTblVar
	WHERE VacationHours < 5  

--確認結果 
SELECT BusinessEntityID, OldVacationHours
, NewVacationHours, ModifiedDate  
FROM @MyTblVar;   


/*作業
更新HumanResources.Employee資料表,依照HireDate正向排序的前10筆資料,將該10筆資料的VacationHours欄位更新為VacationHours+2
*/
UPDATE HumanResources.Employee  
SET VacationHours = VacationHours + 2  
FROM (SELECT TOP (10) BusinessEntityID FROM HumanResources.Employee  
     ORDER BY HireDate ASC) AS th  
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;  






