Access ---> SQL conversion can't get update to work.

[aunixguru]

Access ---> SQL conversion can't get update to work.
Here is my sql:

UPDATE repo INNER JOIN Quotes ON repo.ID = Quotes.RepoID SET repo.InvoiceNum=1064, repo.Rsoldto ='Town Business Center', repo.Rsolddate =4/30/2003, repo.Active = 'Apr 03' WHERE (((Quotes.QuoteNum)=1064));

I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'INNER'.

/Scripts/commitinvoice.asp, line 44

This code worked fine when using it against msaccess database.
Can anyone tell me what is wrong with it?

Thanks.

 

[sobeit]

It's obvious that T-SQL does not support the syntax.
have you tried this instead?

UPDATE repo SET SET repo.InvoiceNum=1064, repo.Rsoldto ='Town Business Center', repo.Rsolddate =4/30/2003, repo.Active = 'Apr 03'
FROM repo R2, Quotes
Where ON R2.ID = Quotes.RepoID
WHERE (((Quotes.QuoteNum)=1064));

 

[tlbroadbent]

Using ANSI Standard syntax for the JOIN, the T-SQL statement should look like the following. Note the single quotes around the date.

UPDATE repo
SET InvoiceNum=1064, Rsoldto ='Town Business Center',
Rsolddate ='4/30/2003', Active = 'Apr 03'
From Repo
INNER JOIN Quotes
ON repo.ID = Quotes.RepoID
WHERE Quotes.QuoteNum=1064;



If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm