Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    10

    Unanswered: Updating records in one table based on another table

    Hey guys, I have two tables Table1 and Table2 in my DB. I need to update certain records in Table1 based on a non match of those specific records in Table2.

    For example,
    if Table1 has three records I need to update (based on certain conditions) "Jim", "Pete" and "Sally" and Table2 has "Pete" and "Sally", I need to update "Jim" in Table 1.

    What is the best way to do that? I am currently using VBA to do this as the conditions are dynamic.

    One of the thoughts I had was to create two recordsets and then loop through both and update one recordset as needed. However; how do I do a nested loops using recordsets?

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE table1
    LEFT OUTER 
      JOIN table2 
        ON table2.keyfld = table1.keyfld
       SET table1.fldx = table2.fldy
     WHERE table2.keyfld IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    10

    Thanks

    Thanks.

    The records I get from table1 (that I need to modify) are themselves a result of a SQL query.

    Then I need to do a non match of the results from this query against table2. and then proceed with the update.

    Sorry for skipping that step!

    How will the overall sql query look now?

    Thanks!

  4. #4
    Join Date
    Sep 2011
    Posts
    10

    to sum it up

    I need to update Records that Exist in One query, but Not in a table. How will the sql query look like?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Data do not exist independently from tables. What you find in a SELECT query comes from one or several tables or is computed from data found in tables (except for constant values). You cannot modify the contents of a SELECT query, except if you modify the data in the underlying table(s) first.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •