SQL Server Delete Query 1

[rwn]

I have this Delete Query that works fine in Access, but in SQL Server Management Studio it shows this error.

QUERY;
UPDATE RFQ INNER JOIN Quote ON RFQ.RFQ = Quote.RFQ SET RFQ.Status = 'Lost', Quote.Status = 'Expired'
WHERE (((RFQ.Status)='active') AND ((Quote.Status)='active') AND ((RFQ.Quote_Date)<='7/1/2010'));

ERROR:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'RFQ'.

 

[gmmastros]

There are a couple of issues here.

1. You can only update data in one table at a time.
2. Your syntax for SQL Server is wrong.

UPDATE RFQ
SET    RFQ.Status = 'Lost'
From   RFQ 
       INNER JOIN Quote 
         ON RFQ.RFQ = Quote.RFQ 
WHERE  RFQ.Status = 'active'
       AND Quote.Status='active'
       AND RFQ.Quote_Date<='7/1/2010';

UPDATE Quote
SET    Quote.Status = 'Expired'
From   RFQ 
       INNER JOIN Quote 
         ON RFQ.RFQ = Quote.RFQ 
WHERE  (RFQ.Status='active' Or RFQ.Status = 'Lost')
       AND Quote.Status='active' AND RFQ.Quote_Date<='7/1/2010';

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom