Hint: Be careful and deactivate AUTOCOMMIT.

The UPDATE command modifies one or more existing rows.

UPDATE <tablename>
SET    <columnname> = <value>,
       <columnname> = <value>,
WHERE  <search_condition>;

Values are assigned to the named columns. Unmentioned columns remain unchanged. The search_condition acts in the same way as in the SELECT command. It restricts the coverage of the command to those rows that satisfy the specified criteria. If the WHERE keyword and the search_condition are omitted, all rows of the table are affected. It is possible to specify search_conditions which hit no rows. In this case no rows are updated - and no error or exception occurs.

As shown previously in the introductory page, the UPDATE command is very flexible. Within one command we can choose an arbitrary combination of the two features:

  • change one or more columns
  • change zero, one or more rows - depending on the search_condition.

Additionally, values can have a dynamic nature like system date, timestamp or timezone - depending on your implementation.

For a recapitulation here is an example:

UPDATE person
SET    firstname = 'Jimmy Walker', 
       lastname  = 'de la Crux' 
WHERE  id = 5;
-- revoke the changes

Further Information

We present some more information about additional opportunities of the UPDATE command here.


Change column remark of row Fishing in table hobby to: 'Catching fishes with or without tools.'.

Click to see solution
-- Change one column of one row
UPDATE hobby
SET    remark = 'Catching fishes with or without tools.'
WHERE  hobbyname = 'Fishing';
-- or: WHERE  id = 2;
-- Check the result
SELECT * FROM hobby;

Change column remark of all rows in table hobby to: 'A creative leisure activity.'. Check the success of the command without COMMIT-ting. Revert the changes.

Click to see solution
-- no WHERE clause ==> all rows affected
UPDATE hobby
SET    remark = 'A creative leisure activity.';
-- Check the result
SELECT * FROM hobby;
-- We don't what to save this values. So we revert the changes.
Find a solution for the previous exercise, which uses a WHERE clause.
Click to see solution
-- A WHERE clauses, which hits all rows
UPDATE hobby
SET    remark = 'A creative leisure activity.'
WHERE  id >= 1;
-- Check the result
SELECT * FROM hobby;
-- We don't what to save this values. So we revert the changes.

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.



Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.

Manage research, learning and skills at defaultlogic.com. Create an account using LinkedIn to manage and organize your omni-channel knowledge. defaultlogic.com is like a shopping cart for information -- helping you to save, discuss and share.

  Contact Us