Modify Data Structure and Alter rows in MySQL
The SQL Modification Statements make changes to base data in tables and columns. There are 3 modification statements:
-
ALTER -- modify columns in a table
-
UPDATE -- modify data in table rows
-
DELETE -- remove rows from tables
​
This again makes it convenient to update massive data with 1 query instead of manually modifying and reuploading new data.
Alter Statement
Let's modify Column RECEIVE_AMT in the 'Collection' dataset to CURRENT_DUE for the purpose of clarity.
​
Alter table Collection RENAME COLUMN RECEIVE_AMT to CURRENT_DUE;
select * from Collection;
​
Observe the 'Collection' data table after running the above query.
The Customer data in 'Collection' table makes more sense now:
Opening_Amt + Current_Due - Payment_Amt = net Outstanding_Amt.
Update Statement
While working on triple data Join, we got 3 employees with ID 12, 13 and 27 in 'Collection' dataset with no customer collection at all. Emp_ID 12 had commission amount =1500, Emp_ID = 1900 and Emp_ID 27 = 100.
Let's update their commission amount to a minimum value "100" since they are the lowest-performing employees.
​
Update Emp set commission = "100" where Emp_ID = "12";
Update Emp set commission = "100" where Emp_ID = "13";
​
SELECT a.Emp_ID, Emp_Name, commission, Department, b.SALE_AMT, c.PAYMENT_AMT, c.OUTSTANDING_AMT
FROM Emp a LEFT OUTER JOIN Sales b ON b.Emp_ID = a.Emp_ID
LEFT OUTER JOIN Collection c ON c.Emp_ID = a.Emp_ID GROUP By a.Emp_ID order by Department;
​
Delete Statement
We can also delete rows from a data table with single query.
​
For example, let's delete the row for Emp_ID 27 from the 'Emp' data
​
Delete FROM Emp WHERE Emp_ID = "27";
select * from Emp order by Department;
​
Notice the updated Emp data below now only have 26 Employees.
