Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Unanswered: SQL Update query help

    I have 2 tables A and B. Due_Date in table B has to be updated by the max(Due_Date) from table A only if a record entry for the same ID is present in Table A, otherwise the date remains the same.


    Table A

    ID Due_Date
    1 2001-01-01
    2 2001-02-01
    3 2002-03-04
    3 2002-09-09
    3 2004-01-01
    5 2003-01-01
    5 2004-03-03

    Table B

    ID Due_Date
    1 2001-01-01
    2
    3 2003-02-02
    4 2001-01-01
    5 2004-01-01
    6 2001-01-01


    Table B (after Update)

    ID Due_Date
    1 2001-01-01(gets updated from table A)
    2 2001-02-01(gets updated from Table A)
    3 2004-01-01(gets updated from Table A)
    4 2001-01-01(since no record in table A, date remains the same)
    5 2004-03-03(gets updated from Table A)
    6 2001-01-01(since no record in table A, date remains the same)

    I need to write a stored procedure for this, input to this stored procedure will be the 'ID'. Please help me out.

    Thanks in advance
    -Rohit

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It'd be a lot easier if you could supply the DDL and DML...

    Code:
    USE Northwind
    GO
    
    CREATE TABLE TableA([ID] int, DueDate datetime)
    GO
    
    INSERT INTO TableA ([ID],DueDate) 
    SELECT 1, '2001-01-01' UNION ALL
    SELECT 2, '2001-02-01' UNION ALL
    SELECT 3, '2002-03-04' UNION ALL
    SELECT 3, '2002-09-09' UNION ALL
    SELECT 3, '2004-01-01' UNION ALL
    SELECT 5, '2003-01-01' UNION ALL
    SELECT 5, '2004-03-03'
    GO
    
    CREATE TABLE TableB([ID] int, DueDate datetime)
    GO
    
    INSERT INTO TableB ([ID],DueDate) 
    SELECT 1, '2001-01-01' UNION ALL
    SELECT 2,  null        UNION ALL
    SELECT 3, '2003-02-02' UNION ALL
    SELECT 4, '2001-01-01' UNION ALL
    SELECT 5, '2004-01-01' UNION ALL
    SELECT 6, '2001-01-01'
    GO
    
    SELECT * FROM TableB
    
        UPDATE B
           SET DueDate = A.Max_DueDate
          FROM TableB B
    INNER JOIN (SELECT [ID], Max(DueDate) AS Max_DueDate 
    	      FROM TableA
    	  GROUP BY [ID]) AS A
    	ON A.[ID] = B.[ID]
    
    SELECT * FROM TableB
    GO
    
    DROP TABLE TableA
    DROP TABLE TableB
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    its working.
    Thanks a lot Brett.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sound suprised...



    Good luck....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •