Help on SQL Statement for Update Query

[KCC47]

I am trying to get an update query to work. Here is the current SQL statement I have:

UPDATE Dealer_Tbl INNER JOIN Import_Tbl ON (Dealer_Tbl.MVDealerStatus = Import_Tbl.Status) AND (Dealer_Tbl.MVDealerExpirationDate = Import_Tbl.Expiration_Date) AND (Dealer_Tbl.MVDealerID = Import_Tbl.License_ID) SET Dealer_Tbl.MVDealerExpirationDate = [Import_Tbl].[Expiration_Date], Dealer_Tbl.MVDealerStatus = [Import_Tbl].[Status], Dealer_Tbl.Marked_As_Expired = 0
WHERE (((Dealer_Tbl.MVDealerExpirationDate)<>[Import_Tbl].[Expiration_Date]) AND ((Dealer_Tbl.MVDealerID)=[Import_Tbl].[License_ID]));


Basically, I'm wanting it to select records that meet two criteria: they have matching MVDealerID/License_ID's and have unmatched MVDealerExpirationDate/Expiration_Dates. The update query will update the expiration date in the dealer_tbl to the value from the import_tbl.

The update query as designed doesn't return any results when it should find 23. I have a select query that returns the records I'm interested in:

SELECT Dealer_Tbl.MVDealerID, Dealer_Tbl.MVDealerName, Dealer_Tbl.MVDealerExpirationDate, Dealer_Tbl.MVDealerStatus, Dealer_Tbl.Marked_As_Expired, Import_Tbl.License_ID, Import_Tbl.Dealer_Name, Import_Tbl.Expiration_Date, Import_Tbl.Status


FROM Dealer_Tbl INNER JOIN Import_Tbl ON (Dealer_Tbl.MVDealerID = Import_Tbl.License_ID) AND (Dealer_Tbl.MVDealerExpirationDate <> Import_Tbl.Expiration_Date);


What is wrong with my first sql statement?

[June7]

Bing: sql update select

Here's one http://stackoverflow.com/questions/2...ing-sql-server

Did you open the SELECT query and then click UPDATE on the ribbon to switch to the update query design?

[KCC47]

I thought I did, here is the SQL statement when I do that:

UPDATE Dealer_Tbl INNER JOIN Import_Tbl ON Dealer_Tbl.MVDealerID = Import_Tbl.License_ID SET Dealer_Tbl.MVDealerExpirationDate = [Import_Tbl].[Expiration_Date], Dealer_Tbl.MVDealerStatus = [Import_Tbl].[Status], Dealer_Tbl.Marked_As_Expired = 0;


But the WHERE clause is missing. It erases it when I convert it to an update query. Is there a better example available, I didn't understand how to translate it to my database.

[June7]

There isn't a WHERE clause in the query you say returns the records you are interested in, just the INNER JOIN. I've never used joins with <> operator. Seems that term is dropped by the UPDATE. Also, the UPDATE is based on a SELECT that joins multiple fields but then you are trying to change values of fields on one side of the join. Maybe if you remove the Status and ExpirationDate fields from the JOIN?

Why are you putting the same data in multiple tables?

[KCC47]

The database has a regular table (dealer_tbl) and an import table (import_tbl) to manage changes in the data. I receive a report regularly with updates to the recordsets in the deale table. This is the best way I know how to manage that data: compare the differences between the two and update the dealer table accordingly.

In the end, the following sql worked:

UPDATE Dealer_Tbl INNER JOIN Import_Tbl ON Dealer_Tbl.MVDealerID = Import_Tbl.License_ID SET Dealer_Tbl.MVDealerExpirationDate = [Import_Tbl].[Expiration_Date], Dealer_Tbl.MVDealerStatus = [Import_Tbl].[Status], Dealer_Tbl.Marked_As_Expired = 0
WHERE Dealer_Tbl.MVDealerExpirationDate <> Import_Tbl.Expiration_Date;

Thank you.