SQL问题 - 如何在SQL中编写具有选择更新语句的脚本 [英] Sql question-how to write a script in SQL which has select update statement

问题描述

我想在表SYSSETTINGVALUE中更新@BeforeValue的值。

查询未提供所需的结果。

如何执行选择和更新在一个单独的脚本中一个接一个地声明？



如果有另一种方式来编写此查询，我将很高兴知道它

 声明  @BeforeValue   int ; 
  set   @ BeforeValue  = DATEDIFF（DAY，DATEADD（yy，DATEDIFF（yy， 0 ，GETDATE（））， 0 ），getdate（））; 
 
 
 选择  @ BeforeValue ，来自 
 SYSSETTING S1，
 SYSSETTINGVALUE OffsetDaysBefore，
 EtpSalesOrg 
 
 其中​​ 
 
 S1.PKey = OffsetDaysBefore.SysSettingPKey 
  AND  S1.Status<> '  d' 
  AND  OffsetDaysBefore.Status<> '  d' 
  AND  EtpSalesOrg.id = OffsetDaysBefore.SalesOrg 
  AND  EtpSalesOrg.status<> '  d' 
 
 开始 
  update  S1，OffsetDaysBefore  set  OffsetDay sBefore.value = '  @ BeforeValue' 其中 S1.PKey = OffsetDaysBefore.SysSettingPKey 
 和 S1.pkey = '  00100000007p3e4a' 和 S1.id = '  TestBefore' 
  end  

我的尝试：

 声明  @ BeforeValue   int ; 
  set   @ BeforeValue  = DATEDIFF（DAY，DATEADD（yy，DATEDIFF（yy， 0 ，GETDATE（））， 0 ），getdate（））; 
 
 
 选择  @ BeforeValue ，来自 
 SYSSETTING S1，
 SYSSETTINGVALUE OffsetDaysBefore，
 EtpSalesOrg 
 
 其中​​ 
 
 S1.PKey = OffsetDaysBefore.SysSettingPKey 
  AND  S1.Status<> '  d' 
  AND  OffsetDaysBefore.Status<> '  d' 
  AND  EtpSalesOrg.id = OffsetDaysBefore.SalesOrg 
  AND  EtpSalesOrg.status<> '  d' 
 
 开始 
  update  S1，OffsetDaysBefore  set  OffsetDay sBefore.value = '  @ BeforeValue' 其中 S1.PKey = OffsetDaysBefore.SysSettingPKey 
 和 S1.pkey = '  00100000007p3e4a' 和 S1.id = '  TestBefore' 
  end  

解决方案

声明@BeforeValue int; 
设置@BeforeValue = DATEDIFF（DAY，DATEADD（yy，DATEDIFF（yy，0，GETDATE（）），0），getdate（））; 
 BEGIN 
 SELECT * FROM SYSSETTING S1 
 INNER JOIN 
 SYSSETTINGVALUE OffsetDaysBefore 
 ON（S1.PKey = OffsetDaysBefore.SysSettingPKey）
 INNER JOIN 
 EtpSalesOrg ON（EtpSalesOrg.id = OffsetDaysBefore.SalesOrg）
 WHERE 
 S1.Status<>'d'和OffsetDaysBefore.Status<>'d'和
 EtpSalesOrg.status<> 'D'; 
 
 UPDATE OffsetDaysBefore SET OffsetDaysBefore.value =@BeforeValue 
来自SYSSETTINGVALUE OffsetDaysBefore 
 INNER JOIN SYSSETTING S1 
 ON（S1.PKey = OffsetDaysBefore.SysSettingPKey）
在哪里S1.pkey ='00100000007p3e4a'和S1.id ='TestBefore'; 
 END; 
 ---------------------------------------------- ---------- 
 DECLARE @a INT =（
选择DATEDIFF（DAY，DATEADD（yy，DATEDIFF（yy，0，GETDATE（）），0），getdate（） ））; 
 
创建表#temp（id int）; 
 INSERT INTO #temp values（1），（5），（6）; 
 
 select * from #temp; 
  -  
 id 
  -  
 1 
 5 
 6 
 
 UPdate #temp set id = @ a where id = 5; 
 select * from #temp 
  -  
 id 
  -  
 1 
 313 
 6 

I want to update the value of @BeforeValue in the table SYSSETTINGVALUE .
the query is not giving the desired result.
How can I execute a select and update statement one after another another in one single script?

If there is another way to write this query ,I will be happy to know it

Declare @BeforeValue int ;
set @BeforeValue =  DATEDIFF(DAY,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),getdate());


select @BeforeValue,from 
 SYSSETTING S1,
 SYSSETTINGVALUE OffsetDaysBefore,
 EtpSalesOrg
 
 where 

 S1.PKey=OffsetDaysBefore.SysSettingPKey
 AND S1.Status<>'d'
 AND OffsetDaysBefore.Status<>'d'
 AND EtpSalesOrg.id=OffsetDaysBefore.SalesOrg
 AND EtpSalesOrg.status<>'d'

 begin
 update S1,OffsetDaysBefore set OffsetDaysBefore.value='@BeforeValue'  where S1.PKey=OffsetDaysBefore.SysSettingPKey 
 and  S1.pkey='00100000007p3e4a' and S1.id='TestBefore'
 end

What I have tried:

Declare @BeforeValue int ;
set @BeforeValue =  DATEDIFF(DAY,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),getdate());


select @BeforeValue,from 
 SYSSETTING S1,
 SYSSETTINGVALUE OffsetDaysBefore,
 EtpSalesOrg
 
 where 

 S1.PKey=OffsetDaysBefore.SysSettingPKey
 AND S1.Status<>'d'
 AND OffsetDaysBefore.Status<>'d'
 AND EtpSalesOrg.id=OffsetDaysBefore.SalesOrg
 AND EtpSalesOrg.status<>'d'

 begin
 update S1,OffsetDaysBefore set OffsetDaysBefore.value='@BeforeValue'  where S1.PKey=OffsetDaysBefore.SysSettingPKey 
 and  S1.pkey='00100000007p3e4a' and S1.id='TestBefore'
 end

解决方案

Declare @BeforeValue int;
set @BeforeValue =DATEDIFF(DAY,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),getdate());
BEGIN
  SELECT * FROM SYSSETTING S1
        INNER JOIN 
        SYSSETTINGVALUE OffsetDaysBefore 
               ON(S1.PKey=OffsetDaysBefore.SysSettingPKey)
       INNER JOIN
       EtpSalesOrg ON(EtpSalesOrg.id=OffsetDaysBefore.SalesOrg)
           WHERE 
              S1.Status<>'d' AND OffsetDaysBefore.Status<>'d' AND 
           EtpSalesOrg.status<>'d';
 
UPDATE OffsetDaysBefore SET OffsetDaysBefore.value=@BeforeValue  
        FROM SYSSETTINGVALUE OffsetDaysBefore 
             INNER JOIN  SYSSETTING S1
         ON( S1.PKey=OffsetDaysBefore.SysSettingPKey )
        WHERE  S1.pkey='00100000007p3e4a' and S1.id='TestBefore';
 END;
--------------------------------------------------------
 DECLARE @a INT=(
select DATEDIFF(DAY,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),getdate())); 

Create table #temp(id int);
 INSERT INTO #temp values(1),(5),(6);

 select * from #temp;
--
id
--
1
5
6

 UPdate #temp set id=@a where id=5;
  select * from #temp
--
id
--
1
313
6

这篇关于SQL问题 - 如何在SQL中编写具有选择更新语句的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！