MS SQL Server - Updating records in one table from another

Author

Updating records in one table from another

cbanks@bjtsupport.com

2006-03-14, 1:23 pm

I receive a weekly download file with both new records and updated
records. Using Bulk Insert I copy all the records into a table I
created to hold these records. Next I do a INSERT INTO from that table
to my live data. Since there is a field used to flag new or updated, I
use that as a WHERE clause.
That part works great; the problem I have is updating using the
following statement:

UPDATE tblClients
SET fldEffdate = download.fldEffdate,
fldPay = download.fldPay,
fldAdd1 = download.fldAdd1,
fldAdd2 = download.fldAdd2,
fldCity = download.fldCity,
fldState = download.fldState,
fldZip = download.fldZip,
fldPhone = download.fldPhone,
fldNewRecord = download.fldNewRecord,
fldDownloadDate = GETDATE()
FROM tblClients
INNER JOIN download
ON download.keyMemberNo = tblClients.keyMemberNo
WHERE download.fldNewRecord = 'N'

Can someone please tell me what I am doing wrong, since no updates or
errors occur.
Thanks
Charles

Jens

2006-03-14, 1:23 pm

Seems that either there are no matching rows, which can be checked with
this:

SELECT COUNT(*)
FROM tblClients
INNER JOIN download
ON download.keyMemberNo = tblClients.keyMemberNo
WHERE download.fldNewRecord = 'N'

or the fields are already updated.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Sponsored Links