Modifying data using SQL

Updating Data in SQL

The UPDATE statement is used to modify existing data in a database.

The syntax of the UPDATE statement is as follows:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

The table name that has to be modified is indicated here by the term table_name. The columns that need to be modified are listed along with their new values in the SET clause. The condition that specifies which rows should be updated is stated in the WHERE clause.

Let's dissect the parts of this assertion:

This term tells the table that you wish to change it.

The name of the table you want to edit is table_name.

SET: Defines the updated columns and their new values.

Values in columns 1 and 2: The columns you want to alter are listed above, along with the new values you want to assign to them. Multiple columns can be specified and are denoted by commas.

WHERE: A supplemental clause that lets you specify requirements for updating particular rows. If left out, the table's entire contents will be updated.

Condition: The circumstance under which it is decided which rows will be updated. Column comparisons with logical operators like AND and OR may be used in combination for this.

Here is an illustration of how to update a table:

UPDATE employees

SET salary = 50000, department = 'Production'

WHERE employee_id = 101;

In this example, we're updating the salary and department columns of the employees table. The WHERE clause specifies that only the row with employee_id equal to 101 will be updated.

Remember to adapt the table name, column names, values, and conditions to match your specific scenario when using the UPDATE statement in SQL.

For example, consider a table called 'customers' with columns 'id', 'name', 'email' and 'phone'. If we want to update the phone number for a customer with id = 1.

Use following SQL statement:

UPDATE customers SET phone = '945-789-1234' WHERE id = 1;

Inserting Data in SQL

The INSERT statement is used to add new data to a table in the database.

The syntax of the INSERT statement is as follows:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Table_name in this context refers to the name of the table to which new data must be added. The column names are optional, however they must be supplied in brackets if they are used. The VALUES clause lists the values to be entered, separated by commas. Consider adding a new customer to the "customers" table as an example. The client's name is Mandeep, and his contact information includes 123-456-7890 and mandeep@domain.com.

Use following SQL statement to add the new customer:

INSERT INTO customers (name, email, phone) VALUES ('mandeep', 'mandeep@domain.com', '123-456-7890');

Deleting Data in SQL

The DELETE statement is used to remove data from a table in the database.

The syntax of the DELETE statement is as follows:

DELETE FROM table_name WHERE condition;

Table_name in this case refers to the name of the table that has to have its data erased. The condition that specifies which rows should be eliminated is specified in the WHERE clause.

For example, suppose to delete a customer with id = 2 from the 'customers' table.

Use following SQL statement to delete the customer:

DELETE FROM customers WHERE id = 2;