SQL Update Table Data

SQL Update rows and columns in a table

As in all the data modification statements, you can in all RDBMS normally the data in only one table at a time.

Now, we will look at two types of updates:

  1. Updating data with constant values.
  2. Update based on data from another table.

Updating data with constant values.

Syntax:
MySQL, Oracle, SQL server, PostgreSQL:
UPDATE table_name
SET column_name1 = expression,  column_name2 = expression, ...
[WHERE search_conditions]
UPDATE example with WHERE statement:
update bookstore
  set Price = price * 0.9
  where publisher='Wrox';

Update based on data from another table.

To achieve this can be very different from the RDBMS to RDBMS.

Below we will show examples of how to achieve this in some selected RDBMS.

Example:
MySQL, Oracle:
UPDATE bookstore a 
       SET a.price = a.price*(SELECT b.priceAdj
                              FROM publisher b
                              WHERE a.publisher=b.publisher)
WHERE EXISTS (SELECT b.priceAdj
              FROM publisher b
              WHERE a.publisher=b.publisher);
Example with the same result:
SQL server:
UPDATE bookstore
SET price = price*priceAdj
FROM bookstore AS a
JOIN publisher AS b
    ON a.publisher=b.publisher;
Example with the same result:
PostgreSQL:
UPDATE bookstore a
SET price = a.price*b.priceAdj
FROM bookstore AS c
JOIN publisher AS b
    ON c.publisher=b.publisher;
Example with the same result:
Oracle:
 UPDATE 
(SELECT a.price AS OLD, a.price*b.priceAdj AS NEW
 FROM bookstore a
 INNER JOIN publisher b
 ON a.publisher=b.publisher
) temp
SET temp.OLD = temp.NEW

If you like to test the last examples you need this:

MySQL, Oracle, SQL server, PostgreSQL:
DROP TABLE  bookstore;

CREATE TABLE bookstore (
  ISBN_NO varchar(15) NOT NULL PRIMARY KEY,
  SHORT_DESC varchar(100)  NULL,
  AUTHOR varchar(40) NULL,
  PUBLISHER varchar(40) NULL,
  PRICE float  NULL
);

/*Data for the table bookstore */

insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('0201703092','The Practical SQL, Fourth Edition','Judith S. Bowman','Addison Wesley',39);
insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('0471777781','Professional Ajax','Jeremy McPeak, Joe Fawcett','Wrox',16);
insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('0596009747','XSLT Cookbook, 2nd Edition','Sal Mangano','OReilly',32);
insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('0672325764','Sams Teach Yourself XML in 21 Days, Third Edition','Steven Holzner','Sams Publishing',49);
insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('0764557599','Professional C#','Simon Robinson and Jay Glynn','Wrox',21);
insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('0764579088','Professional JavaScript for Web Developers','Nicholas C. Zakas','Wrox',17.5);
insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('1590593413','Beginning J2EE 1.4: From Novice to Professional ','James L. Weaver, Kevin Mukhar and  James','Apress',NULL);
insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('1861002025','Professional Visual Basic 6 Databases','Charles Williams','Wrox',19);
insert  into bookstore(ISBN_NO,SHORT_DESC,AUTHOR,PUBLISHER,PRICE) values ('1861006314','GDI+ Programming: Creating Custom Controls Using C#','Eric White','Wrox',14.5);

--DROP TABLE publisher;
CREATE TABLE publisher (
  publisher varchar(40) NOT NULL PRIMARY KEY,
  priceAdj float NULL
);

insert  into publisher(publisher,priceAdj) values ('Wrox',2)
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.