Updating a table from another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This query works in SQL but gives an error if I try to do a Access query.
Anyone know why?

update tickets set [Description] = TicketDetail.TicketDesc
from TicketDetail
where TicketId=TicketNum
 
Are you planning to store this data in more than one table? If so, why?
The whole point of a relational database is to store items once in one
place, then to relate the tables .
 
The exact error I get is "Syntax Error
(missing operator) in query expression 'TicketDetail.Ticketdesc from
TicketDetail'."

Roger Carlson said:
what error does it throw?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Siggi Bjarnason said:
This query works in SQL but gives an error if I try to do a Access query.
Anyone know why?

update tickets set [Description] = TicketDetail.TicketDesc
from TicketDetail
where TicketId=TicketNum
Click to expand...
Click to expand...
 
No I'm moving data from a temp staging table into the production table.

Rick B said:
Are you planning to store this data in more than one table? If so, why?
The whole point of a relational database is to store items once in one
place, then to relate the tables .

--
Rick B



Siggi Bjarnason said:
This query works in SQL but gives an error if I try to do a Access query.
Anyone know why?

update tickets set [Description] = TicketDetail.TicketDesc
from TicketDetail
where TicketId=TicketNum
Click to expand...
Click to expand...
 
Try this instead:

UPDATE tickets INNER JOIN TicketDetail
ON tickets.TicketID = TicketDetail.TicketNum
SET tickets.[Description] = TicketDetail.TicketDesc;

I'm not precisely sure why your version does not work. I know Access will
create a Join in the Where clause in a Select statement, but perhaps it
doesn't work in an Update Statement. There are many similarities between
Access SQL and T-SQL (or Standard SQL), but also many frustrating
differences.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Siggi Bjarnason said:
The exact error I get is "Syntax Error
(missing operator) in query expression 'TicketDetail.Ticketdesc from
TicketDetail'."

Roger Carlson said:
what error does it throw?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Siggi Bjarnason said:
This query works in SQL but gives an error if I try to do a Access query.
Anyone know why?

update tickets set [Description] = TicketDetail.TicketDesc
from TicketDetail
where TicketId=TicketNum
Click to expand...
Click to expand...
Click to expand...
 
Hi Roger

Just wanted to say thank you for this post - it has helped me out with a
very similar query about a query!

;-)

Louise
 
Back
Top