Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 13, 1998

Introduction to Databases for the Web: Pt. 2 - Page 10

By Selena Sol

Modifying Data

It is also easy to modify an existing row using SQL. To do so, you will use the UPDATE operator which follows the basic format of:

UPDATE table_name
SET column_name = some_value
WHERE where_clause;

For example, if we wanted to change the EMPLOYEES table such that Lim Sing Yuen received a $5,000 raise we would say:

UPDATE EMPLOYEES
SET EMP_SALARY = (EMP_SALARY + 5000)
WHERE EMP_NAME = 'Lim Sing Yuen';

or more directly:

UPDATE EMPLOYEES
SET EMP_SALARY = 45000
WHERE EMP_NAME = 'Lim Sing Yuen';

Note also that you can easily modify more than

one column at once in the SET clause. For example, you might say:


UPDATE EMPLOYEES
SET EMP_SALARY = 50000,
    EMP_COMMISSION = '15%'
WHERE EMP_NAME = 'Lim Sing Yuen';

You should also be aware, that if you did not specify a WHERE clause, then the operation affects all rows in the table. Thus the following code would give every employee a $5000 raise:

UPDATE EMPLOYEES
SET EMP_SALARY = (EMP_SALARY + 5000);

Finally, note that the WHERE clause can be as complex as needed, including subqueries if desired. Consider this example in which we give raises to only those employees who make less than the average salary:

UPDATE EMPLOYEES
SET EMP_SALARY = (EMP_SALARY + 1000);
WHERE EMP_SALARY <
   (SELECT AVG(EMP_SALARY)
    FROM EMPLOYEES);

Delete

Finally, SQL provides the DELETE operator for deleting rows from a table. The DELETE operator follows the syntax of:

DELETE FROM table_name
WHERE where_clause;

For example, to delete Lim Li Chuen from the EMPLOYEES table, you would use:

DELETE FROM EMPLOYEES
WHERE EMP_NAME = 'Lim Li Chuen';

Note that multiple rows may be deleted if they match the where clause. Note also that you can easily delete all rows in a table by not specifying a WHERE clause such as:

DELETE FROM EMPLOYEES

Obviously you should be quite careful with DELETE!



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date