March 14, 2007 at 8:43 am
I'm trying to update a table with the following:
UPDATE clean SET clean.branchid = branches.branchid FROM clean WHERE clean.ppi = branches.ppi
I tried it with an INNER JOIN but that also gave me the following error:
syntax error(missing operator) in query expression 'branches.branchid FROM clean'
If you have any ideas, I will gladly hear them. I'm sure it's just a stupid little mistake I made.
Thanks!
March 14, 2007 at 8:54 am
This worked for me...
UPDATE clean
SET clean.branchid = branches.branchid
FROM clean
INNER JOIN branches
ON clean.ppi = branches.ppi
Perhaps you were doing the WHERE instead of the ON?
March 14, 2007 at 9:16 am
Howdy,
Thanks for the reply. I just tried it out and it gave me the missing operator error again. I'm working in access by the way. I'm sure that has something to do with it.
-Ben
March 14, 2007 at 9:47 am
You're right that Access is probably making the difference. I have Access 2003 on my machine (hardly ever use it, though), so I was able to set up a quick database and try a few things. I managed to accomplish this by going into query, design view, and then changing the query type to Update Query. I then put 'branchid' in Field, 'clean' in Table, and '[branches].[branchid]' in Update To. Seemed to work pretty well. I don't know if that functionality is in place for older versions of Access, but I would assume that much is pretty basic.
It looks like the SQL it generated is as follows:
UPDATE clean INNER JOIN branches ON branches.ppi = clean.ppi SET clean.branchid = [branches].[branchid];
Apparently, Access puts the INNER JOIN before the SET. Hope this helps!
March 14, 2007 at 9:56 am
w00T! It worked! Thanks a ton chief! I really appreciate that. I wonder why they changed the syntax order. I knew it had to be something like that.
Thanks again,
-Ben
March 14, 2007 at 4:30 pm
The problem is the code highlighted in red... remove it and it will work fine.
UPDATE clean
SET clean.branchid = branches.branchid
FROM clean
INNER JOIN branches
ON clean.ppi = branches.ppi
Although the following works fine most of the time, it will slam CPU's into the wall if a certain type of parallelism is spawned due to data volume...
UPDATE clean INNER JOIN branches ON branches.ppi = clean.ppi SET clean.branchid = [branches].[branchid];
To avoid the potential slam, if you to a join in the FROM clause of an update in SQL Server, you MUST ensure that the target table is included in the FROM clause.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2007 at 12:49 pm
Resurrecting this thread from the dead because I have questions for you, Jeff. It sounds like you're referring to a SQL Server solution here, but Ben was asking about Access. Was your intended solution actually for Access?
I went ahead and tried your statement without the clean. in my Access database and got the error:
Syntax error (missing operator) in query expression 'branches.branchid FROM clean INNER JOIN branches ON clean.ppi = branches.ppi'.
I think the problem is just that the JOIN syntax for Access is different than SQL Server T-SQL for whatever reason. I haven't been able to find anything as to why there are such huge discrepancies.
Just FYI, the solution that works in Access gets syntax errors in SQL Server, too.
March 30, 2007 at 2:09 pm
The join syntax is different between Access and Sql server.
March 30, 2007 at 10:14 pm
Heh... yeah... how silly of me to think it might have been an SQL Server question on an SQL Server forum... not enough coffee and I missed that... thanks, Julie.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply