Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    408
    Thanks
    17
    Thanked 2 Times in 2 Posts

    search to update several rows

    Can somebody refresh my memory on how to build a query for this.


    I want to use a list of several names (first and last), to update their rows in the table with same information. for example:

    if I have a table set up with the columns:
    [first_name],[last_name],[dob],[married_status]

    I want to find:

    (bob, smith),
    (robert, john),
    (jane, doe);

    and edit their field for [married_status] to 'm'.

    how do I structure this search and replace?

    Thanks!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,449
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Code:
    UPDATE tablename 
    SET married_status = 'm'
    WHERE 
        ( first_name = 'bob' AND last_name = 'smith' )
    OR
        ( first_name = 'robert' AND last_name = 'john' )
    OR
        ( first_name = 'jane' AND last_name = 'doe' )
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,449
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Almost surely there's no point in doing all the names in a single UPDATE.

    Just do one update per person. Simpler to code.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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