Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Update statement not updating all records

    I know the title sounds confusing, but here's the situation. I have the following code:

    TRUNCATE TABLE [Temp_Export];

    INSERT INTO [Temp_Export]
    (
    Temp_Import_ID
    ,[ACCT]
    ,[FNAME]
    ,[MNAME]
    ,[LNAME]
    ,[ADDR_LINE1]
    ,[ADDR_LINE2]
    ,[CITY]
    )
    SELECT
    MAX(Temp_Import_ID) AS Temp_Import_ID, [ACCT],[FNAME],[MNAME],[LNAME],[ADDR_LINE1],[ADDR_LINE2],[CITY]
    FROM Temp_Import tmp INNER JOIN bounced_emails b ON tmp.EMAILADDRESS = b.emailAddress
    WHERE b.hard=1
    AND tmp.xferDt IS NULL
    GROUP BY [ACCT],[FNAME],[MNAME],[LNAME],[ADDR_LINE1],[ADDR_LINE2],[CITY]
    UNION ALL
    SELECT
    MAX(Temp_Import_ID) AS Temp_Import_ID, [ACCT],[FNAME],[MNAME],[LNAME],[ADDR_LINE1],[ADDR_LINE2],[CITY]
    FROM Temp_Import
    WHERE len(rtrim(ltrim(Temp_Import.EMAILADDRESS))) = 0
    AND Temp_Import.xferDt IS NULL
    GROUP BY [ACCT],[FNAME],[MNAME],[LNAME],[ADDR_LINE1],[ADDR_LINE2],[CITY]


    I then create a view as such:

    CREATE VIEW vExport

    AS

    SELECT
    [ACCT],[FNAME],[MNAME],[LNAME],[ADDR_LINE1],[ADDR_LINE2],[CITY]
    FROM Temp_Export

    Then, through my application layer I have code to export a txt file based on the view. At the end of that process I then update those exported records so they're not re-process. The update statement looks like this:

    UPDATE Temp_Import SET
    Temp_Import.xferDt = getDate()
    FROM Temp_Import INNER JOIN Temp_Export ON Temp_Import.Temp_Import_ID = Temp_Export.Temp_Import_ID


    The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. Can someone think of a situation where the view would yield more records than those matched by the update statement?

    TIA

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The FROM clause isn't part of the standard UPDATE statement in SQL, it is an extension introduced long ago by Sybase and still supported by Microsoft today. You really need to either avoid it or learn how Microsoft supports it, because it isn't intuitive to most people.

    Your code ought to work with the following simple change:
    Code:
    UPDATE i
       SET i.xferDt = getDate()
       FROM Temp_Import AS i
       INNER JOIN Temp_Export AS e
          ON i.Temp_Import_ID = e.Temp_Import_ID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    PatP,

    I understand the syntax of my UPDATE statement may be considered non-standard T-SQL, but can you think of a reason why my UPDATE statement is not catching all records being included in the VIEW, though? What I'm seeing is more records being included in the VIEW than those being flagged per the UPDATE statement.

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your UPDATE syntax has three table intances in it (one for Temp_Export and two for Temp_Import), but you're only correlating two of them. In a SELECT, this would become Cartesian but in an UPDATE it can actually cause rows to be excluded due to the name conflict resolution. See the release notes for Microsoft SQL 6.5, they went into some detail on this.

    The syntax the I provided ought to sidestep this problem completely by removing the ambiguity. My UPDATE syntax only has two table instances and those two are correlated.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2003
    Posts
    97
    PatP,

    Thanks for your help. I have updated the code and it looks to have done the trick. I'm trying to find the MS SQL 6.5 release notes where it explains this issue in detail, but can't seem to find it. If you have it handy could you please send me the URL?

    Thanks again

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't know that Microsoft ever posted the SQL 6.5 release notes to the web. They didn't start to routinely post documentation/notes/etc until sometime very late in the 1990s. I'll check to see if I have a machine-readable copy (or any copy for that matter), but don't hold your breath on this one!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Once upon a time i had my hands on a loaner copy of the 6.5 DBA Survival Guide. Might it contain what you are looking for? I still have the one for SQLServer 7.

    A quick looks shows both available on Amazon.com.

Posting Permissions

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