Converting a MsaccessQuery?

[easyit]

Hi,

I'm having trouble getting a query converted to SQLServer.

I've the following query in MSAccess:

UPDATE ZIS0181_import INNER JOIN [SELECT ZIS0181_import.contractrekening
FROM ZIS0181_import
WHERE (((ZIS0181_import.Status2) Like "*X*")) OR (((ZIS0181_import.Status1) Like "*X*"))
]. AS AliasName ON ZIS0181_import.contractrekening = AliasName.contractrekening SET ZIS0181_import.CR_mark_tgv_EL31 = True;

The alias part is not the same, or not possible at all in SQLserver. If split up I have two queries:

SELECT contractrekening FROM  dbo.ZIS0181_import WHERE (Status2 LIKE '%X%') OR (Status1 LIKE '%X%')

Which is stored as vwTmp

And:

UPDATE  dbo.ZIS0181_import
SET  dbo.ZIS0181_import.CR_mark_tgv_EL31 = -1
FROM  dbo.ZIS0181_import, vwTmp
WHERE  dbo.ZIS0181_import.contractrekening = vwTmp.contractrekening

I would like to combine this in one query. How can that be done?



EasyIT

"Do you think that’s air you're breathing?

 

[gmmastros]

Before testing the query that I show below, please make sure you have a good backup of your database just in case something goes wrong.

UPDATE A 
SET    A.CR_mark_tgv_EL31 = 1
FROM   ZIS0181_import As A
       INNER JOIN ZIS0181_import As B
         ON A.contractrekening = B.contractrekening 
WHERE  (A.Status2 Like '%X%') 
       OR (A.Status1 Like '%X%')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Try this:

Update dbo.ZIS0181_import
SET  dbo.ZIS0181_import.CR_mark_tgv_EL31 = -1
FROM  dbo.ZIS0181_import A
INNER JOIN vwTmp B
ON A.<column> = B.<column>
INNER JOIN (SELECT contractrekening FROM  dbo.ZIS0181_import WHERE (Status2 LIKE '%X%') OR (Status1 LIKE '%X%')) C
ON A.<column> = C.<column>
--<insert what columns the table and SELECT get joined on>
WHERE  dbo.ZIS0181_import.contractrekening = vwTmp.contractrekening

Not knowing your columns and stuff, there could be one too many extra joins in that above. A, B and C are aliases and I'm not sure if C is joining to A or B. You'll have to play with it, but that's the basics.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

I came up with basically the same thing as George. I am not sure you need the inner join though, I think that a where clause would do the trick in your case. The inner join could conceivably even cause problems if contractrekening is not your primary key. You might be able to do it like this:

(test it, to see if it gives same result)

UPDATE A 
SET    A.CR_mark_tgv_EL31 = 1
FROM   ZIS0181_import As A
WHERE  (A.Status2 Like '%X%') 
       OR (A.Status1 Like '%X%')

Good Luck,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[easyit]

The solutions work great.

I've chosen the SQL from ALex, since it is the shortest. Also it is not necessary to use an alias at all. Funny how you sometimes can overlook the simplest solutions...

Thank you all!




EasyIT

"Do you think that’s air you're breathing?

 

[bborissov]

You event didn't need FROM:

UPDATE ZIS0181_import
SET    CR_mark_tgv_EL31 = 1
WHERE  (Status2 Like '%X%') OR (Status1 Like '%X%')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

You tested the queries first, right? I don't know your data well enough to be perfectly certain that is the case.

I would hate to see a perfectly good system brought to it's knees because of some stupid comment that I made because I was 'pretty sure'.

Also, remember that shortest does not necessarily equal best.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[bborissov]

Alex,
In that case, we both made one mistake, EasyIT wants:
SET CR_mark_tgv_EL31 = -1
not
SET CR_mark_tgv_EL31 = 1

But you are right, all must be tested veeeery carefully. And with veeery good backup before that.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Ah, I was reading first query, and changed

SET ZIS0181_import.CR_mark_tgv_EL31 = True

. I didn't even look at the 'split' queries.

Wouldn't -1 equate to False in MS Access?

A wise man once said
"The only thing normal about database guys is their tables".

 

[easyit]

Alex,

Thanks for the warning, I'm developing in a virtual PC environment, so no worries.

However I did just remembered why I needed the joins. There are multiple records with the same value in 'contractrekening'. if one meets the criteria, all records should have the field 'CR_mark_tgv_EL31' set to true.



EasyIT

"Do you think that’s air you're breathing?

 

[AlexCuse]

hehe, disaster averted... for now ;-)

A wise man once said
"The only thing normal about database guys is their tables".

 

[bborissov]

Alex,
I will steal your signature


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Go for it Boris, I actually just changed it two seconds before I read your post. I'll probably change back at some point though, that is a classic (courtesy of CajunCenturion).

Ignorance of certain subjects is a great part of wisdom