Converting Access SQL to MS SQL Problem

Question

Post reply

Converting Access SQL to MS SQL Problem

blueknight81215

Grasshopper

Points: 13
More actions
September 24, 2008 at 10:23 am

#193197

Does anyone knows how to convert the below statement into MS SQL? It doesn't looked like an inner join statement to me.
UPDATE TableA, TableB
SET TableB.CapCode = TableA![Cap Code]
WHERE (((TableB.CapCode) Is Null) AND ((TableB.FromDate) Between [Effect Date] And [Term Date])) OR (((TableB.CapCode) Is Null) AND ((TableB.FromDate)>=[Effect Date]) AND ((TableA.[Term Date]) Is Null));

Viewing 6 posts - 1 through 6 (of 6 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Yes it doesn't look like an inner join to me either. Looks more like a cross join, or mayhap I'm just reading it wrong.

what do you think it's supposed to do?

To help us help you read this[/url]For better help with performance problems please read this[/url]

blueknight81215 Grasshopper Points: 13 More actions · Answer 2

I can produce the query into MS SQL Select statement, but I can't figure a way to convert it to an Update Statement.

SELECT * FROM TableA,TableB

WHERE (TableA.CapCode='' AND

(convert(datetime,TableA.FromDate) Between convert(datetime,TableB.EffectDate) And convert(datetime,TableB.TermDate)) OR

(convert(datetime,TableA.FromDate) >= convert(datetime,TableB.EffectDate) AND TableB.TermDate=''))

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

The original syntax didn't work? What error did it give? Have a look at Jeff's excellent article on fixing updates here. http://www.sqlservercentral.com/articles/Performance+Tuning/62278/

To help us help you read this[/url]For better help with performance problems please read this[/url]

Lowell SSC Guru Points: 323573 More actions · Answer 4

i think the update is this:

UPDATE TableA

SET TableA.[Cap Code] = TableB.CapCode

FROM TableB

WHERE (((TableB.CapCode) Is Null) AND ((TableB.FromDate)

Between [Effect Date] And [Term Date]))

OR (((TableB.CapCode) Is Null) AND ((TableB.FromDate)>=[Effect Date])

AND ((TableA.[Term Date]) Is Null));

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

blueknight81215 Grasshopper Points: 13 More actions · Answer 5

Okay I know what the problem is. The Access query I have is missing a join statement. There is a a Table in my Access Database being linked to another Database, which I don't have access to. So when I open up the Query, the link wasn't showing in the Design View and no JOIN statement was found in the SQL View.

I thought the Access Query looked weird, but I'm not too experience with Access either. Thanks for ya help though.