Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcrors_calhoun
Starting Member
22 Posts |
 Posted - 2007-05-18 : 12:07:49
|
| Hi,I am trying to update a table in an Access DB from data in another table. I do this in T-SQL like thisUPDATE LTSET LT.Won = ud.WonFROM League_Table LTINNER JOIN(SELECT teams.player_name AS Player, Count(*) AS WonFROM teams LEFT JOIN match_results ON teams.player_name=match_results.resultWHERE match_results.result<>"Draw"GROUP BY teams.player_name)udONLT.Player = ud.PlayerBut I am getting a syntax error when I try a similar sort of update in Access. I am not very familiar with Access, could someone let me know if it is possible to do an update like this in Access.ThanksMcRors |
|
|
nheidorn
Starting Member
28 Posts |
Posted - 2007-05-18 : 12:46:40
|
Access doesn't directly support embedded subqueries. You have a few options depending on how your queries are implemented.1) Save the UD portion of your SQL as a named query in Access and join the query to League_Table for the update2) Use a SELECT INTO query to output the results of your UD query into a temp table, update League_Table from the temp table, and then drop the temp tableOption one can be completed in a single step, but requires named queries which is a practice I try to avoid whenever possible. Option two requires three steps, but it is often faster and it is the method I use most often.You will also have to change the syntax of your update:UPDATE LTSET LT.Won = ud.WonFROM League_Table LT INNER JOIN ... becomesUPDATE League_Table LT INNER JOIN ...SET LT.Won = ud.Won |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
T-SQL & Access SQL Syntax difference