CODEWITHSUNDEEP
mysql
Arunkumar
Arunkumar

Reputation: 45

list of several names, to update their rows in the table with same information

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!

Upvotes: 1

Views: 41

Answers (3)

arun
arun

Reputation: 106

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' )

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71424

You would use UPDATE query:

UPDATE `table`
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')

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26363

Use the IN operator:

UPDATE myTable
SET married_status = 'm'
WHERE (first_name, last_name) IN (
  ('bob'   , 'smith'),
  ('robert', 'john'),
  ('jane'  , 'doe'))

Upvotes: 2

Related Questions