I have a select join query that will return 2202 records:SELECT *FROM bAPVM V INNER JOIN CV_COMSYS_STAGING.dbo.cnvContactCompany C ON V.VendorGroup = C.CompanyID AND V.Vendor = C.VPSourceIDWHERE V.VendorGroup = 30 AND C.Source = 'CSContactCompany' AND C.Type = 'AP' AND C.VPSourceID IS NOT NULL AND isReady = 1 AND udIsAdded = 'N'
I then use the exact same join to create an update, but it only update 2147 records:UPDATE V SET V.udCNVSource = C.Source, V.udSourceID = C.SourceID, V.udCNVCompanyName = C.CompanyName, V.udContactCompanyID = C.ContactCompanyID, V.udVendor97No = C.Vendor97No, V.udArchengID = C.ArchengID, V.udGenconID = C.GenconID, V.udOwnerID = C.OwnerID, V.udSubsID = C.SubsID, V.udVendorsID = C.VendorsID, V.udGBID = C.GBID, V.udIsPrecon = 'Y', V.udIsAdded = 'N'FROM bAPVM V INNER JOIN CV_COMSYS_STAGING.dbo.cnvContactCompany C ON V.VendorGroup = C.CompanyID AND V.Vendor = C.VPSourceIDWHERE V.VendorGroup = 30 AND C.Source = 'CSContactCompany' AND C.Type = 'AP' AND C.VPSourceID IS NOT NULL AND isReady = 1
I know on occasion I have weird results with update joins... especially if the FROM table isn't the table being updated. But in this case it is and I can't figure out why the join shows all records, but they don't all update.I think somehow this lies with the C.VPSourceID. In this table the VPSourceID can be referenced by multiple records. So I decided to do a DISTINCT query for that field and it comes out to 2147. So first select statement will show all records, but the update seems to only pick up on the distinct list of VPSourceID.Could someone explain why it is doing this and how do I get around it so that all records in bAPVM that do link to VPSourceID via Vendor (same thing).Thanks,Greg
UPDATE via Join not including all records
Posted - 2012-05-31 : 18:49:56
