How to replace all values of a given table in MYSQL at once?

Asked

Viewed 722 times

0

I have a table (produtos) who has the spine product_params

Several products have the value of this table with max_order_level="" and I want to replace everyone for max_order_level="1", i.e., change the empty value of max_order_level to 1.

There’s a way I can do it all at once?

mysql

  • What is the type of column?

  • she is varchar(255)

  • Update product set max_order_level="1"; if you want to do this on all products in the table you do not need Where.

1 answer

1

Yes. There must be several, here is one of them:

You can use the function REPLACE (not to be confused with the commando REPLACE) no update. It will return you the complete original string, only with the searched part replaced.

Thus:

UPDATE produtos
    SET product_params = REPLACE(product_params, 'max_order_level=""', 'max_order_level="1"')  
WHERE product_params LIKE '%max_order_level=""%'

The only restriction would be that the product has this parameter set as you expect (max_order_level=""), only to reduce the amount of records affected to those that will be effectively affected.

See this example working on sql fiddle.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.