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:
- Updating data with constant values.
- 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.