Critical Skill 3.3 Change Table Data

Now we are entering into what can be dangerous territory: changing data. If a small amount of data is changed accidentally , the results are usually easy to repair. You might even do it by hand if you are familiar enough with your data; however, if an unintentional mistake leads you to overwrite every record in a table or even your entire database, the process of returning it to its proper condition can be time-consuming .

To change table data, you can use the REPLACE INTO or UPDATE command. Here, you will learn when they should be used and what should be considered before using them.

Replacing Data

The REPLACE INTO command can be useful under specific circumstances, but if used without forethought, it can change much more than you intended it to change. When you use REPLACE INTO , the whole record is changed. If you do not specify data for all of the record s fields, then the default values for those unspecified fields are inserted into them. Therefore, it is recommended that you use this command only when you need to change an entire record.

The only way to recover from a REPLACE INTO that results in unintentional changes to your data is to have a backup copy of your database available. You will learn about making backups and restoring data from them in Module 8. Also, if the table you are replacing into does not have either a unique index or a primary key, then REPLACE INTO becomes the equivalent of an INSERT command, and it makes no sense to use it.

The REPLACE INTO command works in one of two ways:

• It inserts data into a specified record.

• If the data to be inserted violates key uniqueness, it deletes the existing record first and then inserts the replacement record.

The good news is that using REPLACE INTO to insert a record will keep you from inserting duplicate records into your table, providing they have a unique index or a primary key. The bad news can be that it accomplishes this by unilaterally deleting the existing record in favor of the new record, which it then inserts. If this is your intention , then using REPLACE INTO will save you time by taking two actions from one command. If it was not your intention, you could possibly delete a correct record and insert an incorrect one.

Also, if the table you are replacing into has multiple unique keys and the record you insert conflicts with multiple unique keys, multiple records will be deleted, and that one record will be inserted. In some cases, this may be a desirable result, and once again save you time and effort, but it also illustrates why you should be cautious when using the REPLACE INTO command.

None of this means that you should not use REPLACE INTO . It just means you should be careful and use it only if you are replacing an entire record in a table that has either a unique index (with single or multiple keys) or a primary key.

The syntax for the command is simple:

 REPLACE INTO <  table_name  > (<  field_name1  >, <  field_name2  >, <  field_name3  >, . . .) VALUES("  value1  ", "  value2  ", "  value3  ", . . .); 

Note  

If REPLACE INTO inserts a record, it returns Query OK, 1 row affected . If it deletes a conflicting record, and then inserts its record, it returns Query OK, 2 rows affected . This makes it easy to determine if you are, intentionally or unintentionally, deleting rows while you are adding them.

For example, in a TABLE, fruits , you have columns number , fruit , color , and taste . The record with the unique number 42 contains incorrect information; it says that the color of the fruit , lime, is blue and that the taste is sweet. (42, lime, blue, sweet) The following command replaces the entire record with the correct information:

 REPLACE INTO fruits (number, fruit, color, taste) VALUES("  42  ", "  lime  ", "  green  ", "  sour  "); 

Because this deletes the initial, incorrect record and then replaces it with the new, still unique record, the command will return Query OK, 2 rows affected to indicate the successful completion of the command.

Using the safe-updates Option

As you just learned, the REPLACE INTO command can dangerous, because it can alter an entire record or multiple records unintentionally. The UPDATE command, discussed next , can conceivably be much more destructive. Therefore, before you learn about UPDATE , you need to become familiar with the safe-updates option.

The safe-updates option, when added to your my.ini or my.cnf file, allows MySQL to refuse to run an UPDATE command that does not have a WHERE clause in it referencing a key column to specify which record needs to be changed. This can prevent table-wide updates that might ruin your data.

To add the safe-updates option, open your configuration file in either a word processor or your WinMySQLAdmin window s my.ini Setup tab, or whichever Linux word processor you use, and add the following lines of code at the bottom of the page:

 [mysql] safe-updates 

Make sure you save the changes to your file, and then type EXIT to close your MySQL Client. Then open the MySQL Client in the normal fashion, and that will update your configuration file, making your databases protected by the safe-updates option.

With safe-updates in your configuration file, you will not be able to make a global change to a column ”that is, put the same piece of data in every field in a column. If you need to make such a change, you can return to the command line and turn off the safe-updates option. If you are inside the MySQL Client, type EXIT to return to the command line. Once you are at the shell command line, type the following:

 <  path_to_mysql  > --safe-updates=0; 

The path to the mysql command for Linux and Windows users will be the same as the path used to normally start the MySQL Client.

Updating Data

The UPDATE command allows you to change a single, specific field or several specific fields, in a single record or many records at once. The basic syntax for changing a field or fields in a single record is as follows :

 UPDATE <  table_name  > SET <  field_name  >="data1"[, <  field_name2  >="data2", . . .] [WHERE <  key_field_name  >="  where_value  "]; 

The WHERE clause clarifies which record in the column of the provided field name should be changed, and that field name is usually a key that allows you to narrow the range of which records you want to alter. Without the WHERE clause, data1 would be placed in the specified field of every record in the column of the specified table. MySQL version 4.1 and later will allow you to update multiple tables, which makes the WHERE portion of the code even more important, because you could conceivably update an entire database.

You can also use the UPDATE command to change many records at once, which can be a real timesaver. When you use UPDATE , MySQL does a comparison, and if the value set to update is the same as the value that is already in the field, then it skips the update, which also saves time. The syntax for updating many records at once is as follows:

 UPDATE <  table_name1  >[, <  table_name2  >, . . .] SET <  field_name1  >="  data1  "[, <  field_name2  >="  data2  ", . . .] [WHERE <  key_field_name  >="  where_value  "] [ORDER BY . . .] [LIMIT <  row_count  >]; 

As always, the syntax in the square brackets is optional, but in the case of the WHERE clause, it s only optional when safe-updates is not in effect. The ORDER BY and LIMIT options cannot be used when you are updating multiple tables.

The ORDER BY option allows you to set the order in which the rows will be updated. It is really of use only when used in conjunction with the LIMIT command.

The LIMIT option, which can be used without ORDER BY , sets a specific number of rows to be used in an UPDATE command. In MySQL versions before 4.0.13, the LIMIT option counted affected rows ”that is, rows that satisfied the WHERE clause and had been changed ” and stopped when that count equaled the row_count value. From MySQL version 4.0.13 on, the LIMIT option counts matched rows ”that is, rows that match the WHERE clause criteria, whether or not they have been changed. This allows you to count rows that match the criteria but were not changed because they already had the updated value in the field or fields.

Customer number five in your duck_cust table has incomplete information in the cust_duckbday field. The year and month is there but not the date. (1949-08-00) Complete information can be placed in the field using the UPDATE command, as follows:

 UPDATE duck_cust SET cust_duckbday="1949:08:25" WHERE cust_num=5; 

If you also need to change the data in the suffix field to " V " for the same customer, accomplish both updates using the following command:

 UPDATE duck_cust SET cust_suffix="V", cust_duckbday="1949:08:25" WHERE cust_num=5; 

The cust_zip2 field needs to be changed on customer numbers 9 “14. The following command will update all of those records with the value "1958".

 UPDATE duck_cust SET cust_zip2="1958" WHERE cust_num>=9;