Update table in users_meta in MySQL(Not wordpress)

Question

i want to perform update query a table where i am holding "User Meta Info".

I want to update the meta_value corresponds to "userid" = 9, in which multiple meta_key can be there to update. (This is not wordpress database)

My table looks like this:

umeta_id     userid      meta_key       meta_value
 1              9         mobile        123324
 2              9         address       some address
 3              9         city          some city
 4              9         country       some country
 5              9         occupation    some details
 6              9         website       someurl
 7              9         mobile        123324
 8              9         address       some address
 9              9         city          some city
 10             10        country       some country
 11             10        occupation    some details
 12             10        website       someurl

Please suggest some solution for this.

Ravinder Reddy · Accepted Answer · 2014-01-16 11:09:30Z

1

Update 1:

If i want to update multiple values, which can be any meta_key which is not right now, but can be there. i am using PHP to update this my function looks like this
public static function update
  ( $fullname, $mobile, $interest, $occupation,
    $address, $city, $country, $about, $‌website ) {
  $sql = "update user_meta SET ";
};

You can use either a CASE WHEN or a IF( ... ) condition to update multiple rows of a column.

Following example should help you.

Using IF(...) function:

update user_meta_info
  set
     meta_value = if( meta_key='mobile', '$mobile', meta_value ),
     meta_value = if( meta_key='address', '$address', meta_value ),
     meta_value = if( meta_key='city', '$city', meta_value ),
     meta_value = if( meta_key='country', '$country', meta_value ),
     meta_value = if( meta_key='occupation', '$occupation', meta_value ),
     meta_value = if( meta_key='website', '$website', meta_value )
where
  user_id = 9

Using CASE ... WHEN ...:

update user_meta_info
  set
     meta_value = case meta_key
                        when 'mobile' then '$mobile'
                        when 'address' then '$address'
                        when 'city' then '$city'
                        when 'country' then '$country'
                        when 'occupation' then '$occupation'
                        when 'website' then '$website'
                        else meta_value
                  end
where
  user_id = 9

You have to use meta_key in where clause to set a new value for meta_value column.

update user_meta_info
 set meta_value='new mobile number'
where
 user_id = 9 and
 meta_key = 'mobile'

And in case if each meta_key can have multiple meta values, then you must use umeta_id in where clause. And if umeta_id is a primary key, you can omit using other fields in where clause.

update user_meta_info
 set meta_value='new mobile number'
where
 umeta_id = 7 -- ( and user_id = 9 and meta_key = 'mobile' )

edited Jan 16, 2014 at 11:09

answered Jan 16, 2014 at 8:34

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3201500 Over a year ago

If i want to update multiple values, which can be any meta_key which is not right now, but can be there. i am using PHP to update this my function looks like this public static function

update($fullname,$mobile,$interest,$occupation,$address,$city,$country,$about,$website){ 		$sql = "update user_meta SET "; 	}

;

user3201500 Over a year ago

please check this link too, is possible. stackoverflow.com/questions/21157511/…. Someone already answered but looking for more dynamic way. in which i am not restrict with meta_key, means any number of meta key can be generated into coloum from row. thank you! once again.

Ravinder Reddy Over a year ago

@user3201500: Yes. That solution is pivoting the data. Where as in this update ... it is done in a similar way.

Collectives™ on Stack Overflow

Update table in users_meta in MySQL(Not wordpress)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related