To visit my site

HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#


To contact us

© 2002 - 2015 All Rights Reserved Total Application Works



  • Tell a friend about this site (copy and paste the following HTML page into a file (e.g., using Notepad).)
  • <html>
    <head>
    <title>Example of a link </title>
    </head>
    <body>
    <a href="http://sumtotalz.com/TotalAppsWorks/SQL/MY_SQL_SELECT.html">
    Lists and their Tags </a>
    </table>
    </body>
    </html>



SQL - Update Keyword

Objectives

The objective of the lesson is to describe the SQL UPDATE command and present examples that show how to use it. An example can be found at Tutorial: Basic Client/Server Application IV in the source file tcpServer.java found in zip file . The UPDATE command is used in the source file tcpServer.java on line 516.

Introduction

So far we have used the SQL SELECT command to retrieve data from a database. Once data has been retrieved, it can be displayed or edited within an application program. In previous lessons we have shown how to add and delete a record/row. However, a question arises: "How do we update a record or row that already exists in a database?" In this tutorial, we will describe how data is updated in a database using the UPDATE command.

Update Keyword

The SQL Update command is used to update a row of data in a SQL compliant table. The syntax for this command is:


UPDATE table_name
  SET col_name1 = expression1, col_name2 = expression2, ....
  [ WHERE expression ]
  [ LIMIT limit_amount ]

In my Basic Client Server Address Book application, I use a Java application to update rows or records from my database (see lines 565 to 575 in referenced application tcpServer). Before the update, the table looks like:

 ID  
 FirstName  
 LastName  
 Address  
 City  
 StateOrProvince  
 PostalCode  
 Country  
 EmailAddress  
 HomePhone  
 FaxNumber  
14JulieSmith123 Oak St.RaleighNC27500USAMyID@MyISP919-555-3782919-555-5782
15MaryEasley123 Walnut St.ApexNC27500USAMyID@MyISP919-555-5552919-555-6552
16CatoWillingham123 Jones St.DurhamNC27500USAMyID@MyISP919-555-3492919-555-5492
17MikeJordan123 Saunders St.ApexNC27500USAMyID@MyISP919-555-3882919-555-5882
18CaroleFusemi123 Oak LaneRaleighNC27500USAMyID@MyISP919-555-3472919-555-5472
19PeterWie123 Oak WayGarnerNC27500USAMyID@MyISP919-555-3642919-555-5642
20DonaldHill123 Oak Blvd.CaryNC27500USAMyID@MyISP919-555-3452919-555-5452
21ReginaBell123 Walnut LaneApexNC27500USAMyID@MyISP919-555-3342919-555-5342
22AltheaKeyes123 Walnut WayRaleighNC27500USAMyID@MyISP919-555-3332919-555-5332
23CharlesLawrence123 Whatis St.GarnerNC27500USAMyID@MyISP919-555-3222919-555-5222
24RobertUrengo123 Academy St.DurhamNC27500USAMyID@MyISP919-555-3112919-555-5112
25JohnWieland123 MaIn St.ApexNC27709USAMyID@MyISP(919)-444-9088(919)-444-9089
26KeithMakey404 Walnut Ave.RaleighNC27510USAMyID@MyISP(919)-555-6789(919)-444-9089


In this application, I am using the following form of the SQL UPDATE
  
UPDATE table_name
  SET col_name1 = expression1, col_name2 = expression2, ....
  [ WHERE expression ]
 

Since I am a programmer by profession, I use a program to do my database manipulations. When I use my Basic Client Server Address Book application, to update the database, I see a screen that looks like:



which is the main Client screen. When I push the Update button, I see a screen that looks like:



In this example, I will change the zip code from 27510 to 27513. The code looks like:
  
try {
   statement = connect.createStatement();
   String query = "UPDATE addresses SET " firstname='Keith', 
                      lastname='Makey', 
                      address='404 Walnut Ave.', 
                      city='Raleigh', 
                      stateorprovince='NC', 
                      postalcode='27513', 
                      country='USA', 
                      emailaddress='MyID@MyISP', 
                      homephone='(919)-555-6789', 
                      faxnumber='(919)-444-9089' 
                      WHERE id=26

          int result = statement.executeUpdate( query );
   int result = statement.executeUpdate( query );

   if ( result == 1 )
                  display.append("\nThe record was successful in deleteName()." ) ;

   statement.close();
}
  

In this case, WHERE id=26 leads to a unique ID, and only one record/row is updated.

Summary

  • Use the command UPDATE to modify/change a row of data from a database. It is of the format:
      
    UPDATE table_name
      SET col_name1 = expression1, col_name2 = expression2, ....
      [ WHERE expression ]
     
  • All rows that meet the WHERE clause result will be updated.
  • When performing a update, enclose character values in single quotes.
  • Do not enclose numeric values in single quotes.














HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#

© 2002 - 2015 All Rights Reserved Total Application Works