UPDATE

The UPDATE statement allows you to modify column values in a database.

Syntax

UPDATE table-name [ alias-name ] 
SET column-name = < DEFAULT | expression | subquery > 
[ , column-name = < DEFAULT | expression | subquery >]... 
[ WHERE search-condition ] 

Remarks

INSERT, UPDATE, and DELETE statements behave in an atomic manner. That is, if an insert, update, or delete of more than one row fails, then all insertions, updates, or deletes of previous rows by the same statement are rolled back.

In the SET clause of an UPDATE statement, you may specify a sub-query. This feature allows you to update information in a table based on information in another table or another part of the same table.

You may specify the keyword DEFAULT to set the value to the default value defined for the given column. If no default value is defined, the SRDE uses NULL or pseudo-null value if the column is nullable, otherwise it returns an error. See also, further information on DEFAULT found in INSERT .

The UPDATE statement can update only a single table at a time. UPDATE can relate to other tables via a subquery in the SET clause. This can be a correlated subquery that depends in part on the contents of the table being updated, or it can be a non-correlated subquery that depends only on another table.

Correlated Subquery

UPDATE T1 SET T1.C2 = (SELECT T2.C2 FROM T2 WHERE T2.C1 
= T1.C1) 

Non-correlated Subquery

UPDATE T1 SET T1.C2 = (SELECT SUM(T2.C2) FROM T2 WHERE 
T2.C1 = 10) 

The same logic is used to process pure SELECT statements and subqueries, so the subquery can consist of any valid SELECT statement. There are no special rules for subqueries.

If SELECT within an UPDATE returns no rows, then the UPDATE inserts NULL. If the given column(s) is/are not nullable, then the UPDATE fails. If select returns more than one row, then UPDATE fails.

An UPDATE statement does not allow the use of join tables in the statement. Instead, use a correlated subquery in the SET clause as follows:

UPDATE T1 SET T1.C2 = (SELECT T2.C2 FROM T2 WHERE T2.C1 
= T1.C1) 

All data types for data created prior to Pervasive.SQL 2000 (legacy data) report back as nullable. This means that you can UPDATE NULL into any legacy column type without pseudo-NULL conversion. The following data types are treated as pseudo-NULL by default:

(Normally, when you convert a legacy column to pseudo-NULL, you lose one of the binary values, forfeiting it so that you can query the column for NULL. These data types, however, because of their design, have a different, unique internal value for NULL in addition to their normal data range. With these data types, no binary values are lost if they are converted to NULL so there is no harm considering them as pseudo-NULL by default.)

The rest of the data types are considered "legacy nullable," meaning that NULL may be updated into them. When values are queried, however, the non-NULL binary equivalent is returned. This same binary equivalent must be used in WHERE clauses to retrieve specific values.

The binary equivalents are:

Examples

The following examples updates the record in the faculty table and sets salary as 95000 for ID 103657107.

UPDATE Faculty SET salary = 95000.00 WHERE ID = 103657107 

The following examples show how to use the DEFAULT keyword.

UPDATE t1 SET c2 = DEFAULT WHERE c2 = 'bcd' 
UPDATE t1 SET c1 = DEFAULT, c2 = DEFAULT 

The following example changes the credit hours for Economics 305 in the course table from 3 to 4.

UPDATE Course SET Credit_Hours = 4 WHERE Name = 'ECO 305' 

The following example updates the address for a person in the Person table:

UPDATE Person p 
SET p.Street = '123 Lamar',  
p.zip = '78758',  
p.phone = 5123334444 
WHERE p.ID = 131542520 

Subquery Example A

Two tables are created and rows are inserted. The first table, t5, is updated with a column value from the second table, t6, in each row where table t5 has the value 2 for column c1. Because there is more than one row in table t6 containing a value of 3 for column c2, the first UPDATE fails because more than one row is returned by the subquery. This result occurs even though the result value is the same in both cases. As shown in the second UPDATE, using the DISTINCT keyword in the subquery eliminates the duplicate results and allows the statement to succeed.

CREATE TABLE t5 (c1 INT, c2 INT) 
CREATE TABLE t6 (c1 INT, c2 INT) 
INSERT INTO t5(c1, c2) VALUES (1,3) 
INSERT INTO t5(c1, c2) VALUES (2,4) 
INSERT INTO t6(c1, c2) VALUES (2,3) 
INSERT INTO t6(c1, c2) VALUES (1,2) 
INSERT INTO t6(c1, c2) VALUES (3,3) 
SELECT * FROM t5 

Results:

c1         c2 
---------- ----- 
1          3 
2			 4 
UPDATE t5 SET t5.c1=(SELECT c2 FROM t6 WHERE c2=3) WHERE 
t5.c1=2 - Note that the query fails 
UPDATE t5 SET t5.c1=(SELECT DISTINCT c2 FROM t6 WHERE 
c2=3) WHERE t5.c1=2 - Note that the query succeeds 
SELECT * FROM t5 

Results:

c1         c2 
---------- ----- 
1          3 
3          4 

Subquery Example B

Two tables are created and a variety of valid syntax examples are demonstrated. Note the cases where UPDATE fails because the subquery returns more than one row. Also note that UPDATE succeeds and NULL is inserted if the subquery returns no rows (where NULL values are allowed).

CREATE TABLE T1 (C1 INT, C2 INT) 
CREATE TABLE T2 (C1 INT, C2 INT) 
INSERT INTO T1 VALUES (1, 0) 
INSERT INTO T1 VALUES (2, 0) 
INSERT INTO T1 VALUES (3, 0) 
INSERT INTO T2 VALUES (1, 100) 
INSERT INTO T2 VALUES (2, 200) 
UPDATE T1 SET T1.C2 = (SELECT SUM(T2.C2) FROM T2) 
UPDATE T1 SET T1.C2 = 0 
UPDATE T1 SET T1.C2 = (SELECT T2.C2 FROM T2 WHERE T2.C1 
= T1.C1) 
UPDATE T1 SET T1.C2 = @@IDENTITY 
UPDATE T1 SET T1.C2 = @@ROWCOUNT 
UPDATE T1 SET T1.C2 = (SELECT @@IDENTITY) 
UPDATE T1 SET T1.C2 = (SELECT @@ROWCOUNT) 
UPDATE T1 SET T1.C2 = (SELECT T2.C2 FROM T2) - update fails 
INSERT INTO T2 VALUES (1, 150) 
INSERT INTO T2 VALUES (2, 250) 
UPDATE T1 SET T1.C2 = (SELECT T2.C2 FROM T2 WHERE T2.C1 
= T1.C1) - update fails 
UPDATE T1 SET T1.C2 = (SELECT T2.C2 FROM T2 WHERE T2.C1 
= 5) - Note that the update succeeds, NULL is inserted for all rows of T1.C2 
UPDATE T1 SET T1.C2 = (SELECT SUM(T2.C2) FROM T2 WHERE 
T2.C1 = T1.C1) 

See Also

ALTER TABLE

CREATE PROCEDURE

CREATE TRIGGER

GRANT

INSERT