MySQL MariaDB Update and Replace Tables Command

After we discuss how to access the database and delete tables using MySQL and also Linux Debian/Ubuntu. Now we will try to use some commands that are a little complicated but often used in the implementation of database management.

UPDATE command

A frequently used command is “UPDATE“, this update command is a command that will execute data on the database table so that the data can be updated quickly. Usually the “UPDATE” command will be followed by certain conditions, such as using the “where condition=” clause, where this results in only data that meets the criteria to be changed. However, without using the “where condition=” clause, all data in the selected table will be changed with the new data.

For Example: Change the name on the table “mytable” with “JACK” on the data that has the id number “1“.

UPDATE mytable SET name="JACK" where id="1"

REPLACE command

Then the next command that is a little complicated and rarely used is the “REPLACE” command, the replace command itself is actually almost similar to “UPDATE” as the parent. However, the “REPLACE” command will only replace part/all of the information in the same data as the one that has been specified.

For Example: If letter “A” is found in the data with the id number “1“, it will be replaced by letter “U“.

UPDATE mytable SET name= replace(name, "A","U") where id="1"

The following are examples of “UPDATE” and “REPLACE” commands used to point the old domain to the new domain on the WordPress website. As you can see the command “UPDATE” and followed by “REPLACE” will only change the same data as the data that has been specified and found in the database table.

This command will replace the old domain https://www.old.com with the new domain https://www.new.com.

UPDATE wp_options SET option_value = replace(option_value,'https://www.old.com','https://www.new.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'https://www.old.com','https://www.new.com');
UPDATE wp_posts SET post_content = replace(post_content,'https://www.old.com','https://www.new.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://www.old.com','https://www.new.com');