Sql question-how to write a script in SQL which has select update statement

Question

0.00/5 (No votes)

See more:

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

SQL

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:

SQL

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

Posted 9-Nov-17 20:33pm

Member 12965461

Updated 9-Nov-17 21:48pm

phil.o

v2

Add a Solution

Comments

Santosh kumar Pithani 10-Nov-17 3:35am

why your selected this"@BeforeValue" as column?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Accepted Answer · 2017-11-09T21:48:00

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 question-how to write a script in SQL which has select update statement

1 solution

Solution 1

Add your solution here

Preview 0

Sql question-how to write a script in SQL which has select update statement

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us