June 24, 2005 at 4:19 am
I'm trying to write a query with a if statement with not much luck. what I'm trying to do is update a table dependent on certain criteria. The query that works is :
USE NCN_PTL
Go
UPDATE contracts
Set printedpd = nextperiod,lastprinted = getdate (),nextperiod = nextperiod 1
where claim = 1
But i only want this to run if the lastpd < nextperiod otherwise
I'd like this query to run:
UPDATE contracts
Set nextperiod = null, nextyear = null
where claim = 1
when i try to put an if begin can't get it to work can anyone help.
June 24, 2005 at 5:34 am
This may help
IF EXISTS (SELECT 1
FROM dbo.Contracts
WHERE lastpd < nextperiod AND Claim = 1)
BEGIN
UPDATE contracts
Set printedpd = nextperiod,lastprinted = getdate (),nextperiod = nextperiod 1
where claim = 1
END
ELSE
BEGIN
UPDATE contracts
Set nextperiod = null, nextyear = null
where claim = 1
END
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 24, 2005 at 7:08 am
AJ - your query checks if the rows meeting the criteria exist but do not do a conditional update....
Michael - you may want to try something like this (haven't tested this but it "seems" right..)
IF (SELECT count(*) from contracts where (nextperiod) - (lastpd) > 0) > 0
BEGIN
UPDATE contracts
Set printedpd = nextperiod,lastprinted = getdate (),nextperiod = nextperiod + 1
where claim = 1
END
ELSE
BEGIN
UPDATE contracts
Set nextperiod = null, nextyear = null
where claim = 1
END
**ASCII stupid question, get a stupid ANSI !!!**
June 24, 2005 at 7:27 am
sorry - that doesn't conditionally update either....will get back on this!
**ASCII stupid question, get a stupid ANSI !!!**
June 24, 2005 at 7:31 am
Michael - can you not create a stored procedure and do separate updates based on "(lastpd < nextperiod)" ?!
**ASCII stupid question, get a stupid ANSI !!!**
June 24, 2005 at 7:37 am
okay - if you want a single statement then this should work...
IF (SELECT count(*) from contracts where (nextperiod) - (lastpd) > 0) > 0
BEGIN
UPDATE contracts
Set printedpd = nextperiod,lastprinted = getdate (),nextperiod = nextperiod + 1
where claim = 1 and ((nextperiod) - (lastpd) > 0)
END
ELSE
BEGIN
UPDATE contracts
Set nextperiod = null, nextyear = null
where claim = 1 and and ((nextperiod) - (lastpd) <= 0)
END
however, (imho) - I think you should use a stored procedure!
**ASCII stupid question, get a stupid ANSI !!!**
June 24, 2005 at 8:01 am
I'm just trying to get the query to work then i will put it in an sp
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply