none
How to Include Last() in Update SPROC

    Question

  • I have a sproc that updates a table based on the date of another table which is a one to many relationship. In order for this to work properly I need to get the last row of the order table. I have made multiple attempt at trying to figure it out but am stuck and could use some help with it.
    The information for the order table is:  
    OrderID INT
    OrderDate DateTime
    ProductID int
    Paymentid int
    MemberID int
    
    
    

    Here is the SPROC I commented out the parmeter for testing purposes. Where do I put the LAST or ORDERBY statement to get the last row?
    ALTER PROCEDURE dbo.UpdateMemberStatus
    	/*(@FilterDate DateTime)*/
    	
    AS
    	UPDATE       Members
    	SET                Status = 5
    	FROM            Members INNER JOIN
    	                        [Order] ON Members.MemberID = [Order].MemberID
    	WHERE        ([Order].OrderDate < CONVERT(DATETIME, '1/1/2011', 102)) AND (Members.Status <> 6))
    	RETURN
    

     Thanks in Advance
     

    ---------------------------------------------- Help the community and mark posts as Answered if it helps you out ----------------------------------------------
    Sunday, September 04, 2011 3:42 PM

Answers

  • I'm not sure exactly what you mean by "the last row of the order table".  The following assumes that you mean the the order with the largest datetime that is less than Jan 1, 2011.  Then your update would be

    ;With cte As
    (Select o.MemberID, o.OrderDate, 
      Row_Number() Over (Order By o.OrderDate Desc) As rn
    From [Order] o
    Where o.OrderDate < '20110101')
    Update m
    Set Status = 5
    From Members m
    Inner Join cte c On m.MemberID = c.MemberID And c.rn = 1
    Where m.Status <> 6;
    

    Note that the date format you used 1/1/2011 is not format 102.  It's either format 101 if you are using the US format m/d/y or format 103 if you are using a British (et al) format of d/m/y.  In any case the format where you don't have to specify the date format is yyyymmdd which is what I used.  I would recommend you use it.  But if you need to you the format you are using, then you should change your format code from 102 to 101 or 103.

    Tom

    • Marked as answer by Rollo1002 Sunday, September 04, 2011 4:46 PM
    Sunday, September 04, 2011 4:02 PM
  • Hi, try please :

    ALTER PROCEDURE dbo.UpdateMemberStatus
    /*(@FilterDate DateTime)*/
    AS
    ;With CTE as 
    (select *,ROW_NUMBER() over (partition by MemberID order by OrderDate desc) as rn 
    from [Order] Where C.OrderDate < CONVERT(DATETIME, '1/1/2011', 102)
    )
    UPDATE M
    SET Status = 5
    FROM Members M
    Where Exists (select 1 from CTE C where C.rn=1 and M.MemberID = C.MemberID)
    and M.Status <> 6
    RETURN
    

     

     

    Best regards
    • Marked as answer by Rollo1002 Sunday, September 04, 2011 4:46 PM
    Sunday, September 04, 2011 4:13 PM

All replies

  • I'm not sure exactly what you mean by "the last row of the order table".  The following assumes that you mean the the order with the largest datetime that is less than Jan 1, 2011.  Then your update would be

    ;With cte As
    (Select o.MemberID, o.OrderDate, 
      Row_Number() Over (Order By o.OrderDate Desc) As rn
    From [Order] o
    Where o.OrderDate < '20110101')
    Update m
    Set Status = 5
    From Members m
    Inner Join cte c On m.MemberID = c.MemberID And c.rn = 1
    Where m.Status <> 6;
    

    Note that the date format you used 1/1/2011 is not format 102.  It's either format 101 if you are using the US format m/d/y or format 103 if you are using a British (et al) format of d/m/y.  In any case the format where you don't have to specify the date format is yyyymmdd which is what I used.  I would recommend you use it.  But if you need to you the format you are using, then you should change your format code from 102 to 101 or 103.

    Tom

    • Marked as answer by Rollo1002 Sunday, September 04, 2011 4:46 PM
    Sunday, September 04, 2011 4:02 PM
  • Here is an example from AdventureWorks:

    begin tran
    update c
    set
    	ModifiedDate	= m.OrderDate
    from
    	Sales.Customer	c
    cross apply
    (
    	select top 1
    		*
    	from
    		Sales.SalesOrderHeader	h
    	where
    		h.CustomerID	= c.CustomerID
    	order by
    		h.OrderDate desc
    )	m
    
    rollback tran
    

     


    Tom
    SQL Server MVP
    Toronto, ON Canada
    Sunday, September 04, 2011 4:12 PM
  • Hi, try please :

    ALTER PROCEDURE dbo.UpdateMemberStatus
    /*(@FilterDate DateTime)*/
    AS
    ;With CTE as 
    (select *,ROW_NUMBER() over (partition by MemberID order by OrderDate desc) as rn 
    from [Order] Where C.OrderDate < CONVERT(DATETIME, '1/1/2011', 102)
    )
    UPDATE M
    SET Status = 5
    FROM Members M
    Where Exists (select 1 from CTE C where C.rn=1 and M.MemberID = C.MemberID)
    and M.Status <> 6
    RETURN
    

     

     

    Best regards
    • Marked as answer by Rollo1002 Sunday, September 04, 2011 4:46 PM
    Sunday, September 04, 2011 4:13 PM
  • To provide more clarity on "the last row of the order table", an individual might have multiple or many orders as the years go on. I want to only take the last one OrderID #20 over OrderID # 9 and run the comparison against that Row. Right now the SPROC performs the update as soon as it hits the first order. Does this change the code?

    Thanks for the date / time information this was originally generated by VS 2010 query builder. I will have to use 101 as eventually as this input will be provided by calendar control.

     


    ---------------------------------------------- Help the community and mark posts as Answered if it helps you out ----------------------------------------------
    • Edited by Rollo1002 Sunday, September 04, 2011 4:29 PM
    Sunday, September 04, 2011 4:29 PM
  • >> I have a sproc that updates a table based on the date of another table which is a one to many relationship. <<
    Gee, that's nice, but where is the DDL for these tables? What DRI actions do they have? What are the constraints like? Please, just basic Netiquette and good manners. 
    >> In order for this to work properly I need to get the last row of the order [soc: tables are sets, so they have plural or collective names] table. <<
    How is “last” defined? A sequence number? A timestamp? Tables are not punch cards -- there is no physical ordering. 
    >> I have made multiple attempt at trying to figure it out but am stuck and could use some help with it. <<
    Well, the first problem is that you have no DDL and the specs stink. Here is my guess. Oh, ORDER is a reserved word in SQL, so it is never a data element name even if it were collective
    CREATE TABLE Orders
    (order_nbr INTEGER NOT NULL PRIMARY KEY,
     order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, 
     payment_id INTEGER NOT NULL, 
     member_id INTEGER NOT NULL
      REFERENCES Membership (member_id)
    );
    Apparently, you do not know that we have DATE data types now. 
    >> Here is the SPROC I commented out the parameter for testing purposes. Where do I put the LAST or ORDER BY statement [sic: clauses] to get the last row? << 
    Note that the table is renamed “Membership”, a collective noun, and not “Members” a sequence of records like a deck of punch cards. A magic generic “status”? No, in RDBMS, that is “membership_status”, a particular kind of thing. We use ISO-8601 dates today; You are still using COBOL formats. 
    Your mindset is not in RDBMS yet. You are stuck in 1950's file systems and bad COBOL. 
    I have assumed based on my DDL guess, that the DRI makes “Members.member_id = Orders.member_id” redundant in the sense that we have no orphans. We do not use the proprietary and dangerous UPDATE.. FROM.. Syntax; that was Sybase in the 1980's, not real SQL.  Here is a guess:
    CREATE PROCEDURE UpdateMemberStatus
    AS
    UPDATE Membership
       SET membership_status = 5
     WHERE membership_status <> 6
       AND EXISTS
          (SELECT *
             FROM Orders AS O
            WHERE O.order_date < '2011-01-01'
              AND Membership.member_id = O.member_id);

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Sunday, September 04, 2011 9:35 PM