How do you UPDATE twice within the same query?

Question

I am trying to use UPDATE twice within the same query to revise my TABLE data. Can you explain how I do this? I am getting a sytax error on the following code:

UPDATE HRBI SET HRBI.PayGroupLocationPayGroupCountryCode = IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'VP', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'SVP', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'FEL', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'DIR', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'SFL', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'STR', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'EVP', 'DEU - Exec', 
IIF(HRBI.WorkerID = '45' OR HRBI.WorkerID = '46' OR HRBI.WorkerID = '47' OR HRBI.WorkerID = '48' OR HRBI.WorkerID = '49' OR HRBI.WorkerID = '50' OR HRBI.WorkerID = '51', 'DEU - Exec', HRBI.PayGroupLocationPayGroupCountryCode))))))))
UPDATE SET HRBI.PayGroupCountryDesc = IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec', "Germany - Exec',HRBI.PayGroupCountryDesc);

I tried to add an additional UPDATE to the end of the other UPDATE. Also, is there a way to add a COMMENT within Access SQL? Thanks!

You cannot comment Access SQL, you can run SQL through VBA. You can make life a lot easier with a table of existing values and the value to which the join should be changed. — Fionnuala, Sep 08 '15 at 15:53
MS Access only accepts one statement at a time in SQL (query). In VBA, you can run multiple statements. — Fionnuala, Sep 08 '15 at 17:18
Got it. If I create VBA code to run multiple queries using DoCmd in a module. How do I go about running the code in Access? Thanks! — Chris2015, Sep 08 '15 at 17:25

score 0 · Accepted Answer · answered Sep 08 '15 at 17:40

That is Mad SQL.

Your set statement in effect says if some fields in a row match certain criteria then make the value of HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec' otherwise leave it as it is.

The "certain criteria" should be implemented as a where clause!

So it becomes:

    UPDATE HRBI 
    SET HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec'
    WHERE 
    ( HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' 
      AND 
      HRBI.ManagementLevel IN ('VP', 'SVP', ...etc ...)
    )
    OR
    (    HRBI.WorkerID = '45' OR HRBI.WorkerID = '46' OR HRBI.WorkerID = '47' 
      OR HRBI.WorkerID = '48' OR HRBI.WorkerID = '49' OR HRBI.WorkerID = '50' 
      OR HRBI.WorkerID = '51'
    );

Note you rcould have used HRBI.WorkerID IN (45,46, .....) but I'll leave doing this to you...

You also need to re-write this:

    UPDATE SET HRBI.PayGroupCountryDesc =         
    IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec'
        , "Germany -   Exec'
        ,HRBI.PayGroupCountryDesc);

Perhaps embedding in the first SQL like this

    UPDATE HRBI 
    SET HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec'

       ,HRBI.PayGroupCountryDesc =  "Germany -   Exec'

    WHERE 
    ( HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' 
      AND 
      HRBI.ManagementLevel IN ('VP', 'SVP', ...etc ...)
    )
    OR
    (    HRBI.WorkerID = '45' OR HRBI.WorkerID = '46' OR HRBI.WorkerID = '47' 
      OR HRBI.WorkerID = '48' OR HRBI.WorkerID = '49' OR HRBI.WorkerID = '50' 
      OR HRBI.WorkerID = '51'
    );

OK?

How do you UPDATE twice within the same query?

1 Answers1