Combine Update Queries 4

[ponderdj]

Hello,

I am trying to combine six update queries into one.

They are:

UPDATE tblImport1 SET tblImport1.[SELLING PROGRAM] = "IPR"
WHERE (Left([tblImport1].[LOCALLEASENUMBER],3) Like "IPR");

UPDATE tblImport1 SET tblImport1.[SELLING PROGRAM] = "TRNC"
WHERE (left([tblImport1].[LOCALLEASENUMBER],4) Like "TRNC" Or left([tblImport1].[LOCALLEASENUMBER],4) Like "ATM " Or left([tblImport1].[LOCALLEASENUMBER],4) Like "MUX " Or left([tblImport1].[LOCALLEASENUMBER],4) Like "WAWS");

UPDATE tblImport1 SET tblImport1.[SELLING PROGRAM] = "TRNE"
WHERE (Left([tblImport1].[LOCALLEASENUMBER],4) Like "TRNE" Or (Left([tblImport1].[LOCALLEASENUMBER],4)) Like "ATME ");

UPDATE tblImport1 SET tblImport1.[SELLING PROGRAM] = "TRNP"
WHERE (Left([tblImport1].[LOCALLEASENUMBER],4) Like "ATMP" Or (Left([tblImport1].[LOCALLEASENUMBER],4)) Like "TRNP");

UPDATE tblImport1 SET tblImport1.[SELLING PROGRAM] = "TRNS"
WHERE (Left([tblImport1].[LOCALLEASENUMBER],4) Like "ATMS" Or (Left([tblImport1].[LOCALLEASENUMBER],4)) Like "TRNS");

UPDATE tblImport1 SET tblImport1.[SELLING PROGRAM] = "VTC"
WHERE (Left([tblImport1].[LOCALLEASENUMBER],3) Like "VTC");

Does anyone know the best way to do this?

Thank You

 

[jbpez]

I would keep them separate as they are and have them executed by VBA.

John Borges

 

[BNPMike]

You can do an IIF statement. I would suspect Jet will return all rows for each query as your WHERE is computed, so for big databases it would preferable not to go through the database 6 times.

If you're not bothered, and you don't do VBA, you can run the six queries via a macro.

 

[ponderdj]

I'll probably just run the six in VBA.

Would it be better to create a querydef for each one and then clearing it each time or would it be better running a SqlString? I have seen both ways used before, but am not sure which is more efficient or preferred.

 

[jbpez]

I found that running a SqlString is much faster than creating a querydef and clearing it.

Is the database local(DAO) or remote(ADO) ?

John Borges

 

[r937]

single query version --

update tblImport1 
   set [SELLING PROGRAM] 
     = iif(left([LOCALLEASENUMBER],3) 
           = 'IPR','IPR'
      ,iif(left([LOCALLEASENUMBER],4) 
          in ('TRNC','ATM ','MUX ','WAWS')
                 ,'TRNC'
      ,iif(left([LOCALLEASENUMBER],4) 
          in ('TRNE','ATME'),'TRNE'
      ,iif(left([LOCALLEASENUMBER],4)
          in ('TRNP','ATMP'),'TRNP'
      ,iif(left([LOCALLEASENUMBER],4)
          in ('TRNS','ATMS'),'TRNS'
      ,iif(left([LOCALLEASENUMBER],3)
           = 'VTC','VTC' ))))))
 where left([LOCALLEASENUMBER],3)
        in ('IPR','VTC')
    or left([LOCALLEASENUMBER],4) 
        in ('TRNC','ATM ','MUX ','WAWS'
           ,'TRNE','ATME'
           ,'TRNP','ATMP'
           ,'TRNS','ATMS')

in case you felt like making the database call a bit more efficient



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[ponderdj]

It's local, however I'm managing a different DB w/ an oracle backend. Would that make a difference?

Also, thanks r937!

 

[jbpez]

Is your Access database linked to the oracle tables ? If so then you will need to use DAO to make your updates. If not, then ADO.

John Borges

 

[ponderdj]

I have a DB that uses linked tables and DAO, and was wondering if one way or another of running a query would pass less and be more efficient. On a different note, I went to update a field by using:

UPDATE tblImport1 SET [BUYING PROGRAM] =
Iif(left([PDC], 2) ='YN', 'IPR', Iif(left([PDC], 2) ='YU', 'JWICS', Iif(left([PDC], 2) ='YJ', 'VTC', Iif(left([PDC], 2) ='YG', 'MUX', Iif(left([PDC], 2) ='YA', 'DSN', Iif(left([PDC], 2) ='YT', 'TRNS', Iif(left([PDC], 2) ='YV', 'GSR', Iif(left([PDC], 2) ='YR', 'DRSN', Iif(left([PDC], 2) ='Y1', 'ATM', Iif(left([PDC], 2) ='YQ', 'TRNC', Iif(left([PDC], 2) ='Y2', 'TRNE', Iif(left([PDC], 2) ='YP', 'TRNP', Iif(left([PDC], 2) ='YH', 'BEC', Iif(left([PDC], 2) ='Y6', 'CSCI', Iif(left([PDC], 2) ='YF', 'DMS', Iif(left([PDC], 2) ='YE', 'DITCO', Iif(left([PDC], 2) ='Y7', 'EMSS', Iif(left([PDC], 2) ='YD', 'ADN', Iif(left([PDC], 2) ='YM', 'CCP', Null)))))))))))))))))));

I receive the error, "Expression too complex in query expression..." and it stops with the opening bracket before left([PDC], 2) ='YF', 'DMS', ...

Any Ideas?

 

[PHV]

Have you tried the Switch function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ponderdj]

I haven't, I'm unfamiliar with it but will start reading up now. Thanks

 

[ponderdj]

Sorry to do this but I keep getting syntax errors in my expression either saying comma or missing operator.

Am I on the right track?

UPDATE tblImport1 SET [BUYING PROGRAM] =
Switch(left([PDC], 2) ='YN', 'IPR', (left([PDC], 2) ='YU', 'JWICS', (left([PDC], 2) ='YJ', 'VTC', (left([PDC], 2) ='YG', 'MUX', (left([PDC], 2) ='YA', 'DSN', (left([PDC], 2) ='YT', 'TRNS', (left([PDC], 2) ='YV', 'GSR', (left([PDC], 2) ='YR', 'DRSN', (left([PDC], 2) ='Y1', 'ATM', (left([PDC], 2) ='YQ', 'TRNC', (left([PDC], 2) ='Y2', 'TRNE', (left([PDC], 2) ='YP', 'TRNP', (left([PDC], 2) ='YH', 'BEC', (left([PDC], 2) ='Y6', 'CSCI', (left([PDC], 2) ='YF', 'DMS', (left([PDC], 2) ='YE', 'DITCO', (left([PDC], 2) ='Y7', 'EMSS', (left([PDC], 2) ='YD', 'ADN', (left([PDC], 2) ='YM', 'CCP')))))))))))))))))));

 

[PHV]

And this ?
UPDATE tblImport1 SET [BUYING PROGRAM] =
Switch(left([PDC], 2) ='YN', 'IPR', left([PDC], 2) ='YU', 'JWICS', left([PDC], 2) ='YJ', 'VTC', left([PDC], 2) ='YG', 'MUX', left([PDC], 2) ='YA', 'DSN', left([PDC], 2) ='YT', 'TRNS', left([PDC], 2) ='YV', 'GSR', left([PDC], 2) ='YR', 'DRSN', left([PDC], 2) ='Y1', 'ATM', left([PDC], 2) ='YQ', 'TRNC', left([PDC], 2) ='Y2', 'TRNE', left([PDC], 2) ='YP', 'TRNP', left([PDC], 2) ='YH', 'BEC', left([PDC], 2) ='Y6', 'CSCI', left([PDC], 2) ='YF', 'DMS', left([PDC], 2) ='YE', 'DITCO', left([PDC], 2) ='Y7', 'EMSS', left([PDC], 2) ='YD', 'ADN', left([PDC], 2) ='YM', 'CCP');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ponderdj]

PH,

This runs, but gives me the same 'too complex' error.

 

[PHV]

And what about an update table named, say, tblPDC2BPROG with 2 fields:
[tt]PDC BPROG
YN IPR
YU JWICS
...
YM CCP
[/tt]
Then your one line massive update query:
UPDATE tblImport1 I INNER JOIN tblPDC2BPROG U ON Left(I.PDC,2)=U.PDC SET I.[BUYING PROGRAM]=U.BPROG;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

nice job PHV

you always seem to have a good answer for everything

the name tblPDC2BPROG leaves me cold but the concept is a great idea

star from me

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[ponderdj]

Wow,

Thank you very much PHV and r937. Now that I see the solution, I think, "Why didn't I think of that?" My excuse is that I'm only about a month and a half out of school, so hopefully I'll get better with experience.

Once again, Thank you.

David