Deleting partial information from a table value...

[Jim R]

I have a number of values with wp_1_*rest of information*.  I want to delete the "_1" from each of those values.

 

Possible?

[PFMaBiSmAd]

Assuming your data does not contain any occurrences of wp_1_ you should be able to form an UPDATE query using the REPLACE() function that would change all the occurrences of wp_1_ to anything you want - http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

 

[Jim R]

The syntax is a little confusing.  Am I using UPDATE, REPLACE or SELECT?

 

[PFMaBiSmAd]

UPDATE and REPLACE()

[Jim R]

Still not catching how it works, just because everywhere I look it says something different.  Here is what I tried:

 

UPDATE REPLACE ('meta_key', 'wp_1_', 'wp_')

 

 

[haku]

Are you trying to change the column names, or the data inside the table?

 

If it's the column names, you need to user ALTER. If it's the data inside the table, you haven't told the system what table or what column to update.

[Jim R]

Data inside the column.  I'm trying to make values that start out with wp_1_ and have them just start wp_

 

[PFMaBiSmAd]

An UPDATE query looks like -

 

UPDATE your_table SET your_column = new_value

 

You want the new_value to be the REPLACE() function.

 

P.S. Column names are not enclosed in single-quotes as that makes them a string instead of a column name.

[Jim R]

UPDATE wp_usermeta SET meta_key = REPLACE (meta_key,"wp_1_","wp_")

 

UPDATE wp_usermeta SET meta_key = REPLACE ('meta_key',"wp_1_","wp_")

 

Either of those?

[Jim R]

Because that didn't work. 

[Jim R]

It worked.  I didn't refresh my table. 

 

Here is the correct syntax:

 

UPDATE wp_usermeta SET meta_key = REPLACE (meta_key,'wp_1_','wp_')