Simple SQL syntax question--Update with Inner Join? 1

[jsteph]

Hi,
In MS Access, I can Join two tables and update one with a field value of the other.

Can I, without using EXISTS or IN, do the same syntax in SQL Server? The syntax I've tried (basically identical to MS Access) gives errors. I know how to write the sql with the IN clause but I want to know if it's possible with a Join and that maybe there's just a minor syntactical error I'm making. Thanks,
--jsteph

 

[donutman]

Don't know acess...let's see your attempt.
-Karl

 

[jsteph]

donutman,
Thanks for helping. Here is the sql:

UPDATE tblPunchOriginDistilled 
INNER JOIN tblBadgelist 
ON tblPunchOriginDistilled.poEid = tblBadgelist.FileNum 
SET tblPunchOriginDistilled.poDrv = tblBadgelist.drivernumber

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'INNER'.

...Removing 'INNER' results in:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'JOIN'.

The Identical syntax for identical tables works in Access.
Thanks,
jsteph

 

[PCJock]

Try this instead:

UPDATE tblPunchOriginDistilled,tblBadgelist 
SET tblPunchOriginDistilled.poDrv = tblBadgelist.drivernumber
WHERE tblPunchOriginDistilled.poEid = tblBadgelist.FileNum

 

[jsteph]

PCJock,
With that I get:
Incorrect syntax near ','

...which is the comma on the first line.
It seems like sql server just doesn't support a correlated update, I've tried many different syntax's and just can't seem to get it to work.
--jsteph

 

[PCJock]

Oh crap, I always forget to put the FROM after the set.
Sorry about that, just ignore my previous post.
This should work better:

UPDATE tblPunchOriginDistilled
SET tblPunchOriginDistilled.poDrv = tblBadgelist.drivernumber
FROM tblPunchOriginDistilled,tblBadgelist 
WHERE tblPunchOriginDistilled.poEid = tblBadgelist.FileNum

 

[jsteph]

PCJock,
Thanks, that worked! That has been gnawing at me for a while. I also tried the join after the SET and the below worked too:

UPDATE tblPunchOriginDistilled
SET tblPunchOriginDistilled.poDrv = tblBadgelist.drivernumber
FROM tblPunchOriginDistilled
INNER JOIN tblBadgelist
ON tblPunchOriginDistilled.poEid = tblBadgelist.FileNum

Thanks again!
--jsteph