Showing posts with label SQL INSERT INTO Statement. Show all posts
Showing posts with label SQL INSERT INTO Statement. Show all posts

Sunday, October 18, 2015

SQL INSERT INTO, and Update Statements

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.
The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,...);
 
The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
 
"Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90
Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91
Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
 

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway'); 
 
The selection from the "Customers" table will now look like this:
CustomerID CustomerName ContactName Address City PostalCode Country
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90
Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91
Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway
 
 

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.
The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field will of course also be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
 
The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
 
"Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

SQL UPDATE Example

Assume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.
We use the following SQL statement:

Example

UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';