I need help with a SQL query, help please!!?

Question

I screwed up a field in my SQL 2000 database the other day. Fortunately I have backups of the database. Here is the problem,

I need to write a query that will move the data from one table (the recovered table) to the original table. I have been working with access to have it help me write the sql, but when I put it into enterprise manager i get an error. Here are the specifics, and any help would be appreciated!

Original table (the one I screwed up) = tblOrderDetails

Recovered table (with correct collumn) = tblOrderDetailsRecover

Unique Identifier for records = OrderDetail_ID

screwed up collumn = Price_Sold_At

My query reads like this:

UPDATE tblOrderDetails INNER JOIN

tblOrderDetailRecover ON tblOrderDetails.OrderDetail_ID = tblOrderDetailRecover.OrderDetail_ID

SET tblOrderDetails.Price_Sold_At = [tblOrderDetailRecover].[Price_Sold_At]

WHERE (([tblOrderDetails].[OrderDetail_ID] = [tblOrderDetailRecover].[OrderDetail_ID]...

Again, thanks for the help!

Answer 1

Try this syntax:

UPDATE tblOrderDetails

SET Price_Sold_At = [tblOrderDetailRecover].[Price_Sold_At]

from tblOrderDetails

INNER JOIN

tblOrderDetailRecover ON tblOrderDetails.OrderDetail_ID = tblOrderDetailRecover.OrderDetail_ID

WHERE [tblOrderDetails].[OrderDetail_ID] = [tblOrderDetailRecover].[OrderDetail_ID]