UPDATE JOIN and Between

Question

UPDATE JOIN and Between

chisholmd

SSCrazy

Points: 2490
More actions
March 8, 2005 at 11:17 am

#66056

Hi, The following select statement works as exepcted:
SELECT n.wmsLog.cIp, dbo.GeoIP.startIP, dbo.GeoIP.endIP, dbo.GeoIP.a2
FROM n.wmsLog INNER JOIN
dbo.GeoIP ON n.wmsLog.longIP BETWEEN dbo.GeoIP.startLongIP AND dbo.GeoIP.endLongIP
Where n.wmsLog.pubpoint = 'abcd'
However when I try to use the JOIN..BETWEEN I get the error:
"Incorrect syntax near the keyword 'INNER'"
UPDATE n.wmsLog INNER JOIN dbo.GeoIP ON n.wmsLog.longIP BETWEEN dbo.GeoIP.startLongIP AND dbo.GeoIP.endLongIP
SET n.wmsLog.countrya2 = dbo.GeoIP.a2
Where n.wmsLog.pubpoint = 'abcd'
Any ideas? My immediate suspicion is that SQL does not know that the between statement will only match one row so won't allow the statement.
I am trying to avoid using a cursor for this operation as I want eventually run it as an insert trigger, although in that case I suspose I am only operating on one record at a time and can use:
update...set countryA2 = (select ..from geoIP where)
Thanks for any tips on why my update query fails a syntax check.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I don't think that this is SYTAX-wise correct. You need to do FROM tableA INNER JOIN tableB ON tableA.field = tableB.field WHERE tableA.field BETWEEN tableB.Start AND tableB.END

Good Hunting!

AJ Ahrens

webmaster@kritter.net

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

You need to reference the table being updated in the FROM clause.

See BOL on UPDATE syntax

Looney Tunezez SSC Rookie Points: 36 More actions · Answer 3

UPDATE n.wmsLog

SET n.wmsLog.countrya2 = dbo.GeoIP.a2

FROM n.wmsLog INNER JOIN dbo.GeoIP ON n.wmsLog.longIP BETWEEN dbo.GeoIP.startLongIP AND dbo.GeoIP.endLongIP

WHERE n.wmsLog.pubpoint = 'abcd'

I think this is what you were looking for???

chisholmd SSCrazy Points: 2490 More actions · Answer 4

Thanks guys, I just double checked and the example I was working from online was NOT from MS SQL Doh!

I always have a hard time remembering the syntax for an update based on join, I think maybe I'll save this one a reference file.

Cheers

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)