Monday, February 11, 2013


SQL: Toddlers Query (UPDATE Statement)

This is the second part of our DML (Data Manipulation Language) Statement. UPDATE statement is one of the most critical part second to DELETE statement. I just wanted to remind everyone to be aware on running an update statement in a production environment. Make sure you highlight the correct UPDATE query statement and SET clause along with the WHERE clause.

Basic Syntax:

UPDATE TableName      

SET column-1 = ‘value-1’, column-2 = ‘value-2’, .. column-n = ‘value-n’

WHERE predicate;

I’ve listed down some basic examples of UPDATE statements that you can use to update the columns of your table:

Friends Table

FriendsID
Friends_Number
FirstName
LastName
Country
City
1
7001
Mel
Jacob
Philippines
Quezon
2
7002
CJ
Samuel
Philippines
Valenzuela
3
7003
Jasmin
Tyler
Philippines
Quezon
4
7004
Fred
Dylan
Philippines
Novaliches
5
7005
Joseph
Jonathan
Philippines
Pasig
6
7006
Jennifer
Caleb
USA
Corona
7
7007
Champy
Rivera
New Zealand
Oakland
8
7008
Ivy
Hotdog
New Zealand
Oakland
9
7009
Sydney
Strips
Philippines
Malabon
10
7010
Miguel
Porkchop
Philippines
Mandaluyong

 

Example 1

OBJECTIVE: Run the sample query below to UPDATE the last name of Mel filtered by friends number column with the value of 7001:

UPDATE Friends SET LastName =’Passion’ WHERE Friends_Number =’7001’;

After running update query, run the select statement to view the updated column in Friends table:

SELECT FirstName, LastName, Country WHERE Friends_Number =’7001’;

FirstName
LastName
Country
Mel
Passion
Philippines

NOTE: If you will notice in the original Friends Table, the last name of Mel after updating the Friends table was changed to Passion.

Example 2

OBJECTIVE: Run the sample query below to update the column last name, country and city filtered by Friends_number column with 7002 value:

UPDATE Friends

SET         LastName = ‘Samuel’,

                Country = ‘Australia’,

                City = ‘Sydney’

WHERE Friends_Number = ‘7002’;

To view the update table, run the below query:

SELECT * FROM Friends WHERE Friends_Number =’7002’;

FriendsID
Friends_Number
FirstName
LastName
Country
City
2
7002
CJ
Samuel
Australia
Sydney

 

NOTE: If you will notice in the original Friends Table, the last name, Country and City of CJ were updated after running the update script.

Example 3

OBJECTIVE: Update the column city of “Friends” table with a filtered value of “Quezon”.

UPDATE Friends SET City =’Manila’ WHERE City =’Quezon’;

Run the query below to view the updated Friends Table

SELECT FirstName, LastName, Country, City WHERE City =’Manila’;

FriendsID
Friends_Number
FirstName
LastName
Country
City
1
7001
Mel
Jacob
Philippines
Manila
3
7003
Jasmin
Tyler
Philippines
Manila

 

NOTE: As you will notice, we have updated the City named ‘Quezon’ to ‘Manila’, but we didn’t identify how many users live in the same City (Quezon). In updating a value in a column, we should consider in evaluating the table first before we updating a value in a table. What you can do to check is to count the duplicate records, run the select statement below to find out: (fill-out the table name and column name based on your created table)

SELECT column-1, column-2, .. column-n, count(*)

FROM table

GROUP BY column-1, column-2, .. column-n

HAVING count (*) > 1

No comments:

Post a Comment