Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009

    Unanswered: Update VIA a inner join Problems


    Let me start off by explaining a few things. I have a program that stores information in Access files and it runs on multiple computers through our remote locations. Every hour the program batches the lasts hours information up to our MSSQL server and then waits for all the other locations to batch and then downloads their information.

    Well recently I have been finding data problems. The Program or the batching process. So I have starting to write some triggered jobs in MSSQL. Well I am attempting to build my needed tables and get them populated with their starting information. Here is the SQL Statement I am trying to use.

    UPDATE pp_tracker
    pp_tracker.Starting = t.Prepaid_Left,
    pp_tracker.Starting_Last_MDT = t.min_last_mdt
    SELECT client_no, Prepaid_Left, MIN(last_mdt) as min_last_mdt
    FROM transactions
    ctype <> '2'
    GROUP BY client_no, Prepaid_Left
    ) t
    INNER JOIN pp_tracker
      ON pp_tracker.client_no = t.client_no
    Now let me example what i was expecting it to do and what is really is doing.
    What I was expecting it to do was select the oldest record (last_mdt is a datetime field) out of thousands of records per client in the the transactions table. Then update the pp_tracker table with the that record's values i am looking for. In this case the Prepaid_Left field and the date of that record.

    What this sql statement is actually doing is is looping through multiple records and updating the fields. So when I go and generate a quick report the values are way off.

    Now let me give a example of a single client. If i take the inside select statment and apply just for client 5000806 like so:

    SELECT client_no, Prepaid_Left, MIN(last_mdt) as min_last_mdt
    FROM transactions
    ctype <> '2' AND
    client_no = 5000806
    GROUP BY client_no, Prepaid_Left
    I end up getting this in return:

    client_no   Prepaid_Left       min_last_mdt 
    5000806    NULL                1/2/2009 11:37:16 AM
    5000806    0                     4/14/2005 8:38:21 PM
    5000806    5000806           8/22/2007 12:14:58 PM
    When i just wanted the middle record (one for 4/14/2005 8:38:21 PM).
    When i look at my tracker table after this has run. The client has these values in his fields:
    client_no Starting  	 Starting_Last_MDT
    5000806  5000806	8/22/2007 12:14:58 PM

    I have looked and looked and looked but can not find anywhere how to limit this to just 1 record short of a SELECT TOP 1 statement. Problem with that is I can not be postive that it will be the correct information. As well since it is a sub query I can not define a ORDER BY.

    If any one can help i would greatly appericate it. Also if any of this is confusing or if you have any additional questions ask.
    Last edited by Pyromanci; 03-24-09 at 16:56.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you do not really want an ANSI SQL answer, do you?

    i'm going to move this thread to the Microsoft SQL Server forum, which is where i think it belongs

    holler if you really do want an ANSI SQL solution | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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