Click here to Skip to main content
13,250,182 members (33,670 online)
Rate this:
 
Please Sign up or sign in to vote.
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


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
Posted 9-Nov-17 21:33pm
Updated 9-Nov-17 22:48pm
phil.o96.3K
v2
Comments
Santosh kumar Pithani 10-Nov-17 3:35am
   
why your selected this"@BeforeValue" as column?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
v2
Comments
CHill60 10-Nov-17 4:02am
   
Well done for using the correct method of joining tables and the correct way of updating on a join. However, there is no need for the BEGIN and END. SELECT * is bad practice, listing the columns is far more robust. There is actually no need for the select at all, but that is the OP's issue.
Santosh kumar Pithani 10-Nov-17 4:14am
   
Thank You Chill60!
Your right 'SELECT *' is bad practice and also performance issue but that is OP's problem.
Member 12965461 10-Nov-17 4:33am
   
I do have a question.
In the query ,update query is not working as it is not updating the OffsetDaysBefore.value.
Santosh kumar Pithani 10-Nov-17 5:06am
   
Check "OffsetDaysBefore.value" Datatype it should be int otherwise convert it.
Member 12965461 10-Nov-17 6:08am
   
I converted the datatype then also
only select statement is executing and giving the result.update statement is not executing.
Santosh kumar Pithani 10-Nov-17 6:28am
   
Query is updates with example check it.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04 | 2.8.171114.1 | Last Updated 10 Nov 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100