Pass Through Update Query error (Inner Join)

  • Thread starter Thread starter ILCSP
  • Start date Start date
I

ILCSP

Hello, I hope someone can help me with this.

I have a SQL 2000 Database with 2 tables, Inventory and Orders. We use
a Access 2000 database as our front end and I need to update the second
past due date in the Orders table with today's date. This second
date must be updated only if the first date more than 30 days ago and
if the Inventory entry date is less than 3 months ago. We do it by
states, so we also have codes for them. When I run this pass through
query, I get an error telling me that there's an incorrect syntax
near the keyword 'Inner.' I basically copied the same query we
were using in Access and I deleted most of the parenthesis () there and
hoped it would work, but it does not. Here's the pass through query
syntax:

UPDATE dbo.Orders INNER JOIN dbo.Inventory ON dbo.Orders.INVID =
dbo.Inventory.INVID SET Orders.PastDueDate2 = Date WHERE
Orders.PastDueDate1 >(Date-30) Inventory.StateCode= 27 AND
Inventory.EntryDate<(Date-120)


Can anyone help me figure this out?

Thanks.
 
Try this variation of code. It makes the link in the WHERE instead of with
the I.J.

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
 
Back
Top