📖 MySQL Modifying Table Data

Modifying data in a table is critical to maintaining the integrity and accuracy of your database. While databases store and retrieve information, they are also dynamic, requiring frequent updates to reflect real-world changes. Properly modifying data ensures that your database remains a reliable source of current information.

Creating a New Table Based on an Existing Table

If you want to work with table data without modifying the original data, you may want to create a copy of the table, known as a test table. This practice is common in development environments to prevent unintentional data loss or corruption in production databases.

CREATE TABLE table_name AS select_statement
Required. Identifies the table that contains the data to be copied.
WHERE condition
Optional. Specifies which row(s) are to be copied. If left off, all rows in the table will be updated. Any simple or complex condition may be used.
;
Required. Indicates the end of the SQL statement.
Example
CREATE TABLE customer2
AS 
    SELECT * FROM customer;

You may also need to alter the new table to match the attributes of the old table. For example, we need to add the primary key attribute to the CustomerId field in the new customer2 table.

Example
ALTER TABLE customer2 CHANGE CustomerId CustomerId INT primary key NOT NULL AUTO_INCREMENT;

Dropping a Table

When you've finished testing your data, you may want to remove the test table. Be cautious when dropping tables, as this action is irreversible and will permanently delete all data in the table.

Example
DROP TABLE customer2;

Now we have a table we can use to test our changes without modifying the original data.

Changing Existing Data in a Table

UPDATE table_name
Required. Identifies the table that contains the data to be updated.
SET columnName = value[, columnName2 = value2, . . .]
Required. Specifies the column to be updated and the value that will be stored. If multiple columns are to be updated, separate them with commas.
WHERE condition
Optional. Specifies which row(s) are to be updated. If left off, all rows in the table will be updated. Any simple or complex condition may be used.
;
Required. Indicates the end of the SQL statement.
Example
UPDATE mediatype 
SET 
    Name = 'Cassette Tape'
WHERE
    Name = '8 Track Tape';

Changing a Value in a Column to NULL

If you want to check for NULL values in a WHERE clause, use the IS NULL condition instead of =. Handling NULL values correctly is important because NULL represents the absence of data, and comparing NULL using = will not return the expected results.

UPDATE table_name
Required. Identifies the table that contains the data to be updated.
SET columnName = NULL
Required. Sets the value to NULL.
Note: Do not add quotes (") around the NULL value.
WHERE condition
Optional. Specifies which row(s) are to be updated. If left off, all rows in the table will be updated. Any simple or complex condition may be used.
;
Required. Indicates the end of the SQL statement.
Example
UPDATE mediatype 
SET 
    Name = NULL
WHERE
    Name = 'Cassette Tape';

Adding New Row(s) to an Existing Table

INSERT INTO table_name
Required. Identifies the table that the row will be inserted into.
(column1, column2, column3, . . .)
Optional. Specifies the columns to be inserted. Any columns not listed will contain the default value.
VALUES (column1_value, column2_value, column3_value, ...)
Required. Lists the values to be inserted in the table in sequential order starting with the first column. You must list values for all columns if you leave out the column list.
DEFAULT keyword
If a default value is defined for a column, you may use the DEFAULT keyword in the insert command to populate the inserted rows. This is useful for adding data that has only been partially captured, such as a new customer, or for auto_increment fields.
NULL
If a column is “nullable,” NULL inserts an empty space into the column/row value.
INSERT IGNORE
Useful when populating a table with data from another source that might contain duplicate rows. This command ignores the insertion of rows that would violate unique constraints, such as duplicate primary keys. However, be cautious when using INSERT IGNORE, as it can silently fail to insert data, potentially leading to incomplete records.
;
Required. Indicates the end of the SQL statement.
Example
INSERT INTO mediatype (Name) VALUES ('8 Track Tape');

Adding Multiple Rows to a Table in One Query

INSERT INTO table_name
Required. Identifies the table that the row will be inserted into.
(column1, column2, column3, . . .)
Optional. Specifies the columns to be inserted. Any columns not listed will contain the default value.
VALUES (column1_value, column2_value, column3_value, ...), (column1_value, column2_value, column3_value, ...), (column1_value, column2_value, column3_value, ...)
Required. Lists the values to be inserted in the table in sequential order starting with the first column. You must list values for all columns if you leave out the column list. To add multiple rows, just add a comma and another set of parentheses with the column values for each row.
DEFAULT keyword
If a default value is defined for a column, you may use the DEFAULT keyword in the insert command to populate the inserted rows. This is useful for adding data that has only been partially captured, such as a new customer, or for auto_increment fields.
NULL
If a column is “nullable,” NULL inserts an empty space into the column/row value.
INSERT IGNORE
Useful when populating a table with data from another source that might contain duplicate rows. This command ignores the insertion of rows that would violate unique constraints, such as duplicate primary keys. However, be cautious when using INSERT IGNORE, as it can silently fail to insert data, potentially leading to incomplete records.
;
Required. Indicates the end of the SQL statement.
Example
INSERT INTO mediatype (Name) VALUES ('8 Track Tape'), ('Cassette Tape');

Deleting Existing Rows from a Table

DELETE FROM table_name
Required. Identifies the table that contains the data to be deleted.
WHERE condition
Optional. Specifies which row(s) are to be deleted. If left off, all rows in the table will be deleted. Any simple or complex condition may be used.
;
Required. Indicates the end of the SQL statement.

When deleting rows, always use the WHERE clause to specify which rows to remove. Omitting the WHERE clause will result in all rows being deleted, which could lead to data loss.

Example
DELETE FROM mediatype 
WHERE
    Name = 'Cassette Tape';

Conclusion

Understanding how to modify data effectively is crucial for maintaining the integrity and reliability of your database. Whether you are updating records, inserting new data, or deleting obsolete entries, each operation plays a key role in keeping your database current and accurate. Always proceed with caution and test your queries in a safe environment to avoid unintentional data loss.