SQL STATEMENTS INCLUDING SQL INSERT, SQL UPDATE And SQL DELETE:
SQL INSERT STATEMENT:
The SQL INSERT statement adds one or more new rows to a table. In a simplified version, INSERT has this sql syntax:
INSERT [INTO] table_or_view
[(column_list)]
data_values
Using this syntax you can typically insert data in a single row shown as follows. Here we are using Northwind sample database from Microsoft.
INSERT INTO REGION VALUES ('5','MIDWEST')
If you want to insert data in a table with multiple records, INSERT statement can use a sub-select (sub query) to do just that.
In the next sql insert command example we are trying to insert contact information into CUSTOMER_ CONTACT table from CUSTOMER table. Here’s the transact SQL syntax for insert statement with a sub query:
INSERT CUSTOMER_CONTACT (CUSTOMERID, CONTACTNAME, PHONE)
SELECT CUSTOMERID, CONTACTNAME, PHONE
FROM CUSTOMERS
This will insert only the specific fields, however all the records from the customers table.
Sql insert comman. Here is a screen shot of what this sql command looks like in SQL Server Management Studio (SSMS)
What if you wanted to make a copy of a table and insert data at the same time? No problem. You can do that by using SELECT * INTO. Here is an example of a copy of CUSTOMERS table with a new table is CUSTOMER_CONTACT_BAK. When you use WHERE 1=2, it just creates the table and does not insert any data in the table.
SELECT * INTO CUSTOMER_CONTACT_BAK
FROM CUSTOMERS WHERE 1=2
SQL UPDATE STATEMENT:
The Update SQL command is used to update data in tables. Here is the basic syntax of this important transact sql (tsql) command.
UPDATE TABLE
SET COLUMN=NEW VALUE
WHERE CONDITION
If you use the update statement without the WHERE clause, you will update all the rows in the table with the same value Ooops! Be careful with this sql command for sure.
For the next scenario let us say there is a shortage of seafood in the market, so we need to raise the price by 5% for these items. Go ahead and try this update command on Northwind database.
UPDATE PRODUCTS
SET UNITPRICE=UNITPRICE * 1.05
WHERE CATEGORYID=8
Here is a screen capture of the above tsql command from SSMS,
Just like the SQL INSERT command, you can also use a sub-select command with an SQL UPDATE statement also. The concept is say you are trying to update one table with data from another table. This is commonly used when you are trying to fix the transactional table with values from some lookup table. Let us take a look at this next update sql example now.
UPDATE DBO.CUSTOMER_CONTACT
SET REGION=REGIONDESCRIPTION
FROM DBO.REGION_STATE
WHERE CUSTOMER_CONTACT.STATE=REGION_STATE.STATE
In the above sql query we are updating CUSTOMER_CONTACT table, field REGION with values from REGION_STATE table, field REGIONDESCRIPTION. The catch is that it will only update those rows where there is a match between two tables on the state field.
Here are the copies of the CUSTOMER_CONTACT table before and after update
Before the sql update command
After the update sql command
SQL DELETE STATEMENT:
In five words SQL Delete statement “Removes rows from a table!” Be very careful with this command. Here is the basic syntax of a sql delete statement
DELETE FROM TABLE
WHERE CONDITION
Notice that absence of where clause deletes all rows. Here is an example of a simple delete query command.
DELETE FROM NORTHWIND..REGION
WHERE REGIONDESCRIPTION='MIDWEST'
This delete statement will affect the row that we had added earlier in the INSERT statement. Lets us say that we stopped carrying ay TOFU products, you could get rid of those rows by using the following sql command.
DELETE FROM Products
WHERE ProductName LIKE '%TOFU%'
When we ran this we got the following error:
The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'.
The statement has been terminated.
We have capture the above sql command and the error in the following image
This SQL error has to do with the Primary key and Foreign key relationships. Basically what it is saying is that you have existing customer orders with this product. You would first need to delete the specific rows in ORDER DETAILS table and then delete the “TOFU” rows in PRODUCTS table.
Just like the INSERT And UPDATE statements, DELETE sql statement can also use a sub-select (sub query). Also please note DELETE * FROM TABLE will not work and you have to use DELETE FROM TABLE format.
