Database Systems - INSERT UPDATE and DELETE SQL Statements

INSERT
Add new rows in your table using the INSERT statement. INSERT has 2 clauses:

INTO – the table name and columns where you want to insert the data
VALUES – the values that you want to insert. Can also be used to insert multiple rows, with each one surrounded by parenthesis.

Ex. INSERT INTO Movie VALUES (‘Scarface’, 1983, 20000);

Ex:
INSERT INTO Movie
VALUES (123, ‘Scarface’, 1983, 20000),
(456, ’Ghostbusters’. 1984, 45000);

If you don’t have an auto-incrementing ID column, then be sure to also insert that into your new row. Also be careful of any column constraints, such as trying to insert NULL data into columns that are NOT NULL.

UPDATE

You can update or modify existing rows in a table using the UPDATE statement. It uses the SET clause for specifying the new column values, and the WHERE clause to specify a condition for determining which rows to update. If you don’t include the WHERE clause, then it will update all rows.

UPDATE Movie
SET ReleaseDate = 2022
WHERE ID = 123;

DELETE

To delete an existing row in a table, use the DELETE statement. The FROM clause specifies which table to delete the row from, and the WHERE clause specifies the conditions on which rows to delete. If you don’t include the WHERE clause, then all rows from that table will be deleted. Alternatively, you can use the TRUNCATE statement to delete all rows from a table.

DELETE FROM Movie
WHERE Title = ‘Scarface’;

TRUNCATE TABLE Movie;

NOTE: if you are unable to delete and get error code 1175, run this command to allow deletes:
SET SQL_SAFE_UPDATES = 0;

Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!