1

I have very simple query that is not working and I get error:

'Syntax Error (missing operator) in query expression Tabela2.SALES2 FROM Tabela2'

Here is the code:

UPDATE Tabela1 
SET Tabela1.SALES = Tabela2.SALES2 
FROM Tabela2 
WHERE Tabela1.ID = Tabela2.ID

I want to run this query from VBA/Excel on Acces database (2007). Others queries with e.g. SELECT are working fine, so the problem is only with the query. And I really don't know why it is not working.

luchaninov
  • 6,792
  • 6
  • 60
  • 75
hakubaa
  • 169
  • 1
  • 4
  • 14
  • is the double quote at the end `where` line part of the original query? – Luis LL Aug 12 '13 at 08:20
  • see [this](http://stackoverflow.com/questions/707371/sql-update-set-one-column-to-be-equal-to-a-value-in-a-related-table-referenced-b) –  Aug 12 '13 at 08:20
  • No, there is no quote. It's only mistake in above code. – hakubaa Aug 12 '13 at 08:32

4 Answers4

4

An UPDATE query using FROM is possible in SQL Server, but not in MS Access. Use this instead:

UPDATE Tabela1 INNER JOIN Tabela2 ON Tabela1.ID = Tabela2.ID 
SET Tabela1.Sales = [Tabela2].[Sales2];
Adi Inbar
  • 12,097
  • 13
  • 56
  • 69
Poonam
  • 669
  • 4
  • 14
  • Thanks, It's working fine:) but mayby you know also way the query with FROM is not? Is it sth with Access ? – hakubaa Aug 12 '13 at 08:40
  • Query using "from" is not possible in access.But it's possible in sql. – Poonam Aug 12 '13 at 08:48
  • @user2674174 If your question has been answered, please [mark the answer that you feel best addressed your question as accepted](http://stackoverflow.com/help/someone-answers). You can also [upvote](http://stackoverflow.com/help/why-vote) the accepted answer and any other answers you found useful. – Adi Inbar Apr 10 '14 at 05:09
0

UPDATE Tabela1 SET Tabela1.SALES = Tabela2.SALES2 FROM Tabela1,Tabela2 WHERE Tabela1.ID = Tabela2.ID

0

try this

UPDATE Tabela1 
SET Tabela1.SALES = Tabela2.SALES2 
FROM Tabela1 
INNER JOIN Tabela2 
WHERE Tabela1.ID = Tabela2.ID
Luis LL
  • 2,912
  • 2
  • 19
  • 21
-1
Update TABLE2, TABLE1
SET TABLE2.SALES2 = TABLE1.SALES
WHERE TABLE2.ID=TABLE1.ID

hey friends try this 100% working. As per poonam FROM statement is not possible and its true but no need to inner join and make your query slow down.
This SQL Query will run on MS Access only.

Rupam
  • 1
  • 2
Rupam
  • 1