Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    16

    Question Unanswered: SQL UPDATE statement with variable in WHERE clause

    Hello,

    I have a SQL UPDATE statement with a variable in the WHERE clause that isn't filtering .. it updates all records instead of just the ones where [Table2].[Field2] = strField2.

    DoCmd.RunSQL "UPDATE [Table1], [Table2] " & _
    "SET [Table1].[Field1] = [Table2].[Field1] " & _
    "WHERE [Table2].[Field2] = '" & strField2 & "';"

    The strField2 variable is set as a string, the value is alpha-numeric.
    I confirmed that strField2 does equal the value in [Field2] and fields in both tables match type, size etc.

    Can't seem to figure it out .. any help would be great.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that where clause will always return true so it will always update all rows

    what you need is to link (or JOIN) table1 to table 2
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Posts
    16
    i've been trying multiple versions of this:

    DoCmd.RunSQL "UPDATE [Table1]" & _
    "INNER JOIN [Table2] " & _
    "ON [Table1].[Field1] = [Table2].[Field1] " & _
    "SET [Table1].[Field1] = [Table2].[Field1], " & _
    "[Table1].[Field2] = [Table2].[field2] " & _
    "WHERE [Table2].[Field1] = '" & strField1 & "';"

    with no luck, it doesn't error but it also doesn't update any records .. just not getting it, kind of new to writing sql statements .. previous ones were just simple select statements .. i may be close here .. just not quite

    also .. just to mention .. [Field1] is text and [Field2] is memo .. from what i understand, can't join memo fields, but this should be ok?
    Last edited by snoodledorf; 12-10-13 at 22:06. Reason: addl info

  4. #4
    Join Date
    Oct 2011
    Posts
    16
    Finally figured it out.
    Turns out I needed to reference a 3rd field and 3rd variable .. also seemed to be missing the parentheses in the WHERE clause.

    This is what worked:

    DoCmd.RunSQL _
    "UPDATE [Table1], [Table2] " & _
    "SET [Table1].Field1 = [Table2].[Field1], [Table1].Field2 = [Table2].[Field2] " & _
    "WHERE ((([Table1].[Field3])= '" & strField3 & "')) " & _
    "AND ((([Table2].[Field1])= '" & strField1 & "'));"

Tags for this Thread

Posting Permissions

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