Data Manipulation - SQL
Categories
Modify Data
To modify data in SQL you will use the UPDATE statement. The UPDATE statement allows you to change existing records in a table based on the specified criteria you give it.
Syntax:
Syntax-1
UPDATE
table_name
SET
column1 = value1, column2 = value2
WHERE
condition
In Syntax-1, you specify the table_name that you want to update in and then the column or columns that you want to update with a specific value in SET. Then you specify the condition that the column[s] must meet in order to for their values to get updated.
Syntax-2
UPDATE
table_name
SET
column1 = value1, column2 = value2
In Syntax-2, you specify the table_name that you want to update in and then the column or columns that you want to update with a specific value in SET but do not specify a condition for the updates.
WARNING!: If you do not include a WHERE statement and specify the conditions for the update, all the rows will be updated with the new information.
Example:
Example-1
UPDATE
company.employees
SET
first_name = 'Frank'
WHERE
id = 101
In Example-1, you are updating the company.employees table and setting the first_name of the person with an id 101 to Frank. Since id is a column with unique values, only 1 row will get updated with the new first_name.
Example-2
UPDATE
company.employees
SET
first_name = 'Richard', salary = 120000
WHERE
id = 417
In Example-2, you are updating the company.employees table and setting the first_name to Richard and salary to 120000 for id 417. Since id is a column with unique values, only 1 row will get updated with the new first_name and salary.
Example-3
UPDATE
company.employees
SET
job_title = 'Manager 2'
WHERE
job_title = 'Manager'
In Example-3, you are updating the company.employees table and setting the job_title to Manager 2 for all the rows with a job_title of Manager. Since job_title is not a column with only unique values, it is possible that more than 1 row will get updated with the new job_title.
Data Before Updates:
Data After Updates:
Add Data
To add data in SQL you will use the INSERT INTO statement. The INSERT INTO statement allows you to insert one or more new rows into a table.
Syntax:
Syntax-1
INSERT INTO
table_name (column1, column2, ...)
VALUES
(value1, value2, ...)
In Syntax-1, you specify the table_name that you want to insert data into and specify the column or columns in which you want to insert the values into. Then you specify the actual value or values that you want inserted into the corresponding columns.
WARNING!: It is optional to specify the column or columns when you are inserting values into all the columns but it is good practice to specify all of the columns regardless.
Example:
Example-1
INSERT INTO
company.employees
VALUES
(201, 'Justin', 'Analyst', 1, 105000)
In Example-1, you are inserting a new row into the company.employees table and since you are including a value for each column, you do not need to specify the columns of the table. The values need to be in the same order as their appropriate column.
Example-2
INSERT INTO
company.employees (id, first_name, job_title, department_id, salary)
VALUES
(301, 'Kent', 'Analyst', 1, 90000)
In Example-2, you are inserting a new row into the company.employees table and although we are inserting a value for each column, we will specify each column name because it is best practice. This example is the same as Example-1 but with the column names included.
Example-3
INSERT INTO
company.employees (id, first_name, salary)
VALUES
(401, 'Glenn', 115000)
In Example-3, you are inserting a new row into the company.employees table but we do not have a value for each column so we need to specify which columns we want the values to go into.
Data Before Updates:
Data After Updates:
Delete Data
To delete data in SQL you will use the DELETE FROM statement. The DELETE FROM statement allows you to remove one or more rows into a table based on a specified condition.
Syntax:
Syntax-1
DELETE FROM
table_name
WHERE
condition
In Syntax-1, you specify the table_name that you want to remove data from and specify the condition of the rows you want to remove.
Syntax-2
DELETE FROM
table_name
In Syntax-2, you specify the table_name that you want to remove data from and since there is no specified condition, all of the rows in the table will be deleted.
WARNING!: Always double check your DELETE statements as it permanently removes data from the table.
Example:
Example-1
DELETE FROM
company.employees
WHERE
id = 201
In Example-1, you are deleting all of the rows in the company.employees table that has the id of 201, since the id column is a primary key, only one row will end up getting deleted.
Example-2
DELETE FROM
company.employees
WHERE
job_title = 'Analyst'
In Example-2, you are deleting all of the rows in the company.employees table that have the job_title of Analyst, since the job_title column is not a primary key, more than one row can end up getting deleted.
Example-3
DELETE FROM
company.employees
In Example-3, you are deleting all of the data in the company.employees table because you did not specify any specific conditions for the deletion.
NOTE: I did not run this query for the example because I would’ve deleted all of the data in the table.
