Post reply

Compining UPDATE queries

  • October 23, 2008 at 12:26 pm

    #77120

    Need to update multiple tables in an Access dB and want to combine 2 querys into one but keep getting a syntax error. Here is the two UPDATE I would like to combine. What are the steps or modifications I need to make to make it one query instead of two.

    UPDATE EmployeeDataFeed SET EmployeeDataFeed.FirstName = "Test", EmployeeDataFeed.LastName = "Participant", EmployeeDataFeed.Address1 = "111 ABC Road", EmployeeDataFeed.Address2 = " ", EmployeeDataFeed.Address3 = " ", EmployeeDataFeed.City = "Richmond", EmployeeDataFeed.State = "VA", EmployeeDataFeed.ZipCode = "22124", EmployeeDataFeed.HomePhone = "(555)555-5555", EmployeeDataFeed.BirthDate = #1/1/1970#, EmployeeDataFeed.PlanCompensation = "$90,000", EmployeeDataFeed.ActualCompensation = "$90,000";

    UPDATE Beneficiary SET Beneficiary.FirstName = "Test", Beneficiary.AddressLine1 = "222 road", Beneficiary.AddressLine2 = " ", Beneficiary.City = "Rochmond", Beneficiary.State = "Va", Beneficiary.ZipCode = "22124", Beneficiary.LastName = "Dependent", Beneficiary.BirthDate = #1/1/1965#;

  • October 23, 2008 at 12:46 pm

    #888811

    You're updating two different objects, so you'll need two update statements.

    Why do you need to combine them?

    Unless you want all the rows in those two tables to be the values you've listed, you'll need to restrict the updates with a 'where' clause.

    And then again, I might be wrong ...
    David Webb

  • October 23, 2008 at 1:29 pm

    #888839

    Thanks for the reply. I want to combine because I really have 8 different tables that I need to scrub in a database and while I can run each fine I would rather run one and go have a coke:)

  • October 23, 2008 at 1:30 pm

    #888841

    Sorry got ahead of myself but you probley picked it up when I said scrub that yes I want all rows updated

  • October 23, 2008 at 2:18 pm

    #888868

    I'm sorry, Access isn't my area of expertise, but can't you type them all in one query window and execute them all serially by just clicking 'execute' on the window?

    Or save them all as separate queries and execute them from code behind a button?

    And then again, I might be wrong ...
    David Webb

  • October 23, 2008 at 2:23 pm

    #888873

    well I don't know I'm far from an expert either. As they say there are many ways to skin a cat so I keep trying to find a more efficient way to run 8 quiries instead of selecting each and selecting run. Very boring and time consuming. 😀

  • October 24, 2008 at 6:07 am

    #889145

    You can write code to run each of the queries in turn - you can even write a macro to do this, but I don't like macros so wouldn't recommend it

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

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