Editing Data and Tables in SQL

6
0

Introduction

An employee joins a company, receives a salary revision, and eventually leaves. Each event changes the stored records, but it does not necessarily change the table that holds them.

A new requirement, such as recording an employee’s email address, is different: the table needs another column. SQL provides separate commands for changing records and changing their structure.

Set Up the Example Table

The following table definition works in both MySQL and PostgreSQL:

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10, 2) NOT NULL,
CONSTRAINT chk_salary_nonnegative
CHECK (salary >= 0)
);

The primary key identifies each employee. The salary constraint rejects negative amounts, while department permits NULL when the department has not been supplied.

INSERT: Add New Records

Use INSERT when an employee does not yet have a record. Specify the column names so the relationship between columns and supplied values is clear.

INSERT INTO employees
(emp_id, name, department, salary)
VALUES
(301, 'Neha Sharma', 'Sales', 55000.00);

Several records can be added in one statement:

INSERT INTO employees
(emp_id, name, department, salary)
VALUES
(302, 'Ishan Gupta', 'Engineering', 90000.00),
(303, 'Priya Singh', 'Support', 45000.00);

The table now contains:

emp_id

name

department

salary

301

Neha Sharma

Sales

55000.00

302

Ishan Gupta

Engineering

90000.00

303

Priya Singh

Support

45000.00

An ordinary insert using an existing emp_id fails because it would violate the primary key. When that conflict should update an existing record, use an upsert.

UPSERT: Insert or Update

Suppose a payroll feed supplies employee 301 with a salary of 57000.00. The intended behavior is to insert the employee if absent or update the salary if that employee already exists.

MySQL

INSERT INTO employees
(emp_id, name, department, salary)
VALUES
(301, 'Neha Sharma', 'Sales', 57000.00)
AS incoming
ON DUPLICATE KEY UPDATE
salary = incoming.salary;

PostgreSQL

INSERT INTO employees
(emp_id, name, department, salary)
VALUES
(301, 'Neha Sharma', 'Sales', 57000.00)
ON CONFLICT (emp_id)
DO UPDATE SET
salary = EXCLUDED.salary;

Run the version for your database. With the existing example data, Neha’s salary changes from 55000.00 to 57000.00. Her name and department stay unchanged because the update assignment specifies only salary.

MySQL responds to a conflicting primary or unique key. The PostgreSQL example explicitly selects emp_id as its conflict target.

UPDATE: Change Existing Values

Use UPDATE when the record already exists. SET specifies the new values, and WHERE determines which records qualify.

UPDATE employees
SET department = 'Customer Success',
salary = 48000.00
WHERE emp_id = 303;

This changes Priya’s department and salary. A primary-key condition identifies one record, while a broader condition can intentionally update several:

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering';

Ishan’s salary becomes 94500.00, a 5% increase. The expression uses the salary currently stored in each qualifying row.

Without WHERE, every row qualifies:

UPDATE employees
SET department = 'HR';

That statement assigns HR to every employee. Before running a targeted update, checking the same condition with SELECT helps reveal whether it matches the intended records.

DELETE: Remove Records

DELETE removes complete rows. For example, to remove Priya’s record:

DELETE FROM employees
WHERE emp_id = 303;

To remove several known employees, a condition can use IN:

DELETE FROM employees
WHERE emp_id IN (301, 302);

Without a condition, DELETE FROM employees; removes every row while leaving the table available. Foreign-key rules may reject a deletion or apply a configured action to related records.

Unlike UPDATE, DELETE does not clear individual column values. To remove a department value while retaining the employee, use UPDATE to set that nullable column to NULL.

Editing Records in SQL

Editing Records in SQL

ALTER TABLE: Change the Structure

ALTER TABLE changes the table definition. The examples below illustrate separate changes to the original employees table.

Add and Populate a Column

Suppose the company starts collecting email addresses:

ALTER TABLE employees
ADD COLUMN email VARCHAR(150);

This nullable column has no explicit default, so existing rows receive NULL. Adding the column does not supply email addresses; that requires a data update:

UPDATE employees
SET email = 'neha@example.com'
WHERE emp_id = 301;

Populating a newly added column for existing records is called backfilling. This example shows the distinction directly: ALTER TABLE creates the column, and UPDATE supplies its value.

ALTER TABLE vs UPDATE

ALTER TABLE vs UPDATE

Rename or Modify Columns

Both systems support renaming a column:

ALTER TABLE employees
RENAME COLUMN name TO employee_name;

Changing a column’s type uses different syntax.

MySQL

ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2) NOT NULL;

PostgreSQL

ALTER TABLE employees
ALTER COLUMN salary TYPE DECIMAL(12, 2);

MySQL’s MODIFY redefines the column, so retain attributes such as NOT NULL when they should continue to apply. Its CHANGE clause can rename and redefine a column together:

ALTER TABLE employees
CHANGE COLUMN name employee_name
VARCHAR(100) NOT NULL;

Use either rename example, not both. PostgreSQL uses separate rename and type-change operations instead of MySQL’s CHANGE syntax.

Both systems also support dropping a column and renaming a table:

ALTER TABLE employees
DROP COLUMN email;

ALTER TABLE employees
RENAME TO staff;

Dropping email removes its stored values. Renaming the table preserves its records, but subsequent statements must use staff.

DELETE, TRUNCATE, or DROP?

These commands serve different purposes. The examples here use the original table name employees.

Command

Effect

Supports WHERE?

Table remains?

DELETE

Removes matching rows, or all rows without a condition

Yes

Yes

TRUNCATE TABLE

Empties the table

No

Yes

DROP TABLE

Removes the table definition and its data

No

No

To clear a reusable practice table:

TRUNCATE TABLE employees;

To remove the table itself:

DROP TABLE employees;

TRUNCATE is useful when the entire dataset will be replaced. Its transaction behavior differs: MySQL truncation causes an implicit commit and cannot be undone with an ordinary rollback, while PostgreSQL permits rollback within an uncommitted transaction. Dependencies can also restrict truncation or dropping.

Summary

INSERT, UPSERT, UPDATE, and DELETE handle changes to records. Choose the command according to whether a row is new, already exists, needs different values, or should be removed.

ALTER TABLE changes the structure, TRUNCATE clears the records, and DROP TABLE removes the table. Keep those purposes distinct, and check the scope of a change before applying it.

CS Core

Read Similar Blogs

Comments0