SQL Newbie - if ..begin ??

  • 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.

  • 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

  • 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 !!!**

  • sorry - that doesn't conditionally update either....will get back on this!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Michael - can you not create a stored procedure and do separate updates based on "(lastpd < nextperiod)" ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • 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 !!!**

  • 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