Hey guys-
I have 2 tables I am working with. The first one is the raw data I am importing (tbImport)- it includes records that are NEW, as well as records that are existing in the 2nd table. Some of the existing records will have updated info in this table, and will need the corresponding records updated as such.
The 2nd table is the main database table. It holds pre-existing records (tbListing).
I have read that I can do a RIGHT JOIN on an UPDATE query, and it will not only update the existing records, but- will append any new records to the tbListing table. I'm running the query- and it's saying that it's going to return/update ZERO records. There are 28,000+ records that will need to be updated at this time. What is going on here? WHy won't it update?
Currently, there are NO records in the pre-existing DB (I realize that this should just cause it to append all the new records on it- but tomorrow's update will append AND update at the same time).
I have the Primary Key set and matched in both tables (LN2 & MLS). Here's the LOOOONG SQL... Can you help me out here? I'm very frustrated!
THANK YOU!!!
UPDATE tbImport RIGHT JOIN tbListing ON tbImport.LN2 = tbListing.MLS SET tbImport.LN2 = tbListing.MLS, tbImport.PROPTYPE = tblisting.PropertyType, tbImport.ST = tblisting.Status, tbImport.HSN1 = tblisting.HouseNumber, tbImport.CP = tblisting.Compass, tbImport.STR = tblisting.StreetName, tbImport.SC = tblisting.Suffix, tbImport.UN = tblisting.Unit, tbImport.ADDRESS = tblisting.Address, tbImport.CT = tblisting.City, tbImport.ZP = tblisting.Zip, tbImport.Z4 = tblisting.Zip4, tbImport.COU = tblisting.County, tbImport.SBD = tblisting.Subdivision, tbImport.GRID = tblisting.Grid, tbImport.AREA = tblisting.Area, tbImport.BR = tblisting.Beds, tbImport.BATHS = tblisting.Baths, tbImport.SQ = tblisting.SqFt, tbImport.[LEVEL] = tblisting.Level, tbImport.YR = tblisting.YearBuilt, tbImport.PPOL = tblisting.Pool, tbImport.BOOKNUMINT = tblisting.Book, tbImport.MA = tblisting.MapNum, tbImport.PN = tblisting.Parcel, tbImport.PL = tblisting.ParcelLetter, tbImport.BOOK_PG = tblisting.BookPage, tbImport.LT = tblisting.LotNumber, tbImport.OLP = tblisting.OriginalListPrice, tbImport.LP = tblisting.ListPrice, tbImport.COE = tblisting.COE, tbImport.SP = tblisting.SoldPrice, tbImport.TN = tbListing.Occupant, tbImport.FP = tbListing.Fireplace, tbImport.ROOF = tbListing.Roof, tbImport.PPOL1 = tbListing.PrivatePool, tbImport.LTSZ = tbListing.LotSize, tbImport.LS = tbListing.LotDim, tbImport.PRK = tbListing.Parking, tbImport.DTYP = tbListing.DwelProj, tbImport.HRSE = tbListing.Horses, tbImport.BD = tbListing.Builder, tbImport.HS = tbListing.HighSchool, tbImport.HSD = tbListing.HighSchoolDistrict, tbImport.TRM1 = tbListing.[MortgageTerms], tbImport.MISC = tbListing.Misc, tbImport.PROP = tbListing.PropertyDescription, tbImport.APSF = tbListing.SqFtRange, tbImport.OWNS = [tbListing].[Ownership], tbImport.[ON] = [tbListing].[OwnerName], tbImport.OT = [tbListing].[OwnerPhone], tbImport.TY = [tbListing].[TaxYr], tbImport.SHOW = [tbListing].[Show], tbImport.RM1 = [tbListing].[Remarks], tbImport.RM7 = [tbListing].[RealtorRemarks], tbImport.TX = [tbListing].[Taxes], tbImport.CB = [tbListing].[CoBroke], tbImport.LD = [tbListing].[ListDate], tbImport.PND = [tbListing].[PendDate], tbImport.MARKETDATE = [tbListing].[OffMarketDate], tbImport.SD = [tbListing].[ChangeDate], tbImport.CD = [tbListing].[ContractDate], tbImport.ATEDISPLAY = [tbListing].[ExpirationDate], tbImport.CDOM = [tbListing].[CDOM], tbImport.ADOM = [tbListing].[ADOM], tbImport.DOM = [tbListing].[DOM];
I have 2 tables I am working with. The first one is the raw data I am importing (tbImport)- it includes records that are NEW, as well as records that are existing in the 2nd table. Some of the existing records will have updated info in this table, and will need the corresponding records updated as such.
The 2nd table is the main database table. It holds pre-existing records (tbListing).
I have read that I can do a RIGHT JOIN on an UPDATE query, and it will not only update the existing records, but- will append any new records to the tbListing table. I'm running the query- and it's saying that it's going to return/update ZERO records. There are 28,000+ records that will need to be updated at this time. What is going on here? WHy won't it update?
Currently, there are NO records in the pre-existing DB (I realize that this should just cause it to append all the new records on it- but tomorrow's update will append AND update at the same time).
I have the Primary Key set and matched in both tables (LN2 & MLS). Here's the LOOOONG SQL... Can you help me out here? I'm very frustrated!
THANK YOU!!!
UPDATE tbImport RIGHT JOIN tbListing ON tbImport.LN2 = tbListing.MLS SET tbImport.LN2 = tbListing.MLS, tbImport.PROPTYPE = tblisting.PropertyType, tbImport.ST = tblisting.Status, tbImport.HSN1 = tblisting.HouseNumber, tbImport.CP = tblisting.Compass, tbImport.STR = tblisting.StreetName, tbImport.SC = tblisting.Suffix, tbImport.UN = tblisting.Unit, tbImport.ADDRESS = tblisting.Address, tbImport.CT = tblisting.City, tbImport.ZP = tblisting.Zip, tbImport.Z4 = tblisting.Zip4, tbImport.COU = tblisting.County, tbImport.SBD = tblisting.Subdivision, tbImport.GRID = tblisting.Grid, tbImport.AREA = tblisting.Area, tbImport.BR = tblisting.Beds, tbImport.BATHS = tblisting.Baths, tbImport.SQ = tblisting.SqFt, tbImport.[LEVEL] = tblisting.Level, tbImport.YR = tblisting.YearBuilt, tbImport.PPOL = tblisting.Pool, tbImport.BOOKNUMINT = tblisting.Book, tbImport.MA = tblisting.MapNum, tbImport.PN = tblisting.Parcel, tbImport.PL = tblisting.ParcelLetter, tbImport.BOOK_PG = tblisting.BookPage, tbImport.LT = tblisting.LotNumber, tbImport.OLP = tblisting.OriginalListPrice, tbImport.LP = tblisting.ListPrice, tbImport.COE = tblisting.COE, tbImport.SP = tblisting.SoldPrice, tbImport.TN = tbListing.Occupant, tbImport.FP = tbListing.Fireplace, tbImport.ROOF = tbListing.Roof, tbImport.PPOL1 = tbListing.PrivatePool, tbImport.LTSZ = tbListing.LotSize, tbImport.LS = tbListing.LotDim, tbImport.PRK = tbListing.Parking, tbImport.DTYP = tbListing.DwelProj, tbImport.HRSE = tbListing.Horses, tbImport.BD = tbListing.Builder, tbImport.HS = tbListing.HighSchool, tbImport.HSD = tbListing.HighSchoolDistrict, tbImport.TRM1 = tbListing.[MortgageTerms], tbImport.MISC = tbListing.Misc, tbImport.PROP = tbListing.PropertyDescription, tbImport.APSF = tbListing.SqFtRange, tbImport.OWNS = [tbListing].[Ownership], tbImport.[ON] = [tbListing].[OwnerName], tbImport.OT = [tbListing].[OwnerPhone], tbImport.TY = [tbListing].[TaxYr], tbImport.SHOW = [tbListing].[Show], tbImport.RM1 = [tbListing].[Remarks], tbImport.RM7 = [tbListing].[RealtorRemarks], tbImport.TX = [tbListing].[Taxes], tbImport.CB = [tbListing].[CoBroke], tbImport.LD = [tbListing].[ListDate], tbImport.PND = [tbListing].[PendDate], tbImport.MARKETDATE = [tbListing].[OffMarketDate], tbImport.SD = [tbListing].[ChangeDate], tbImport.CD = [tbListing].[ContractDate], tbImport.ATEDISPLAY = [tbListing].[ExpirationDate], tbImport.CDOM = [tbListing].[CDOM], tbImport.ADOM = [tbListing].[ADOM], tbImport.DOM = [tbListing].[DOM];
