Help with Select/Join/Update 2

[cisscott]

Im trying to do a fairly simple update where i have to join two tables, and I keep getting the error

"The column prefix event2 does not match with a table name or alias used in the query"

I know I just have a problem with the syntax of the query, and would appreciate any advice.. Here is the code I am trying to use:

select e.eventid,e.event,e.year,et.eventid,et.time
from event2 as e inner join eventtimes as et
   on e.eventid = et.eventid

update eventtimes 
set eventtimes.time = '02:00:00PM'
where event2.year = '2005' and event2.event like 'KM%' and eventtimes.eventid = 9017

 

[Catadmin]

Don't use AS for your aliases. Just do:

Event2 e inner join eventtimes et ...

AS is looking for a column name to alias, not a table.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Pattycake245]

Yes, basically you are referencing a table event2 which is not in your update query. You need to join the tables in your update:

update a
set a.time = '02:00:00PM'
from eventtimes a join event2 b 
on a.eventid=b.eventid
where b.year = '2005' and b.event like 'KM%' and a.eventid = 9017

Tim