Update statement not updating all records

Last Post 30 Jul 2012 07:33 PM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
Balance
New Member
New Member

--
27 Jul 2012 01:39 PM
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
gunneyk
New Member
New Member

--
30 Jul 2012 07:33 PM
You don't have the Temp_Import_ID column listed in the view definition so I don't know how it is working at all. Why even bother with a view that is a simple select like that?
You are not authorized to post a reply.

Acceptable Use Policy