UPDATE

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

Syntax

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

table-name ::= user-defined-name 
view-name ::= user-defined-name 
alias-name ::= user-defined-name (Alias-name is not allowed if a 
FROM clause is used. See FROM Clause .) 
table-reference ::= { OJ outer-join-definition } 
| [db-name.]table-name [ [ AS ] alias-name ] 
| [db-name.]view-name [ [ AS ] alias-name ] 
| join-definition 
| ( join-definition ) 
| ( table-subquery )[ AS ] alias-name [ (column-name [ , column-name 
]... ) ] 

outer-join-definition ::= table-reference outer-join-type JOIN table-reference ON search-
condition 
outer-join-type ::= LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ 
OUTER ] 
search-condition ::= search-condition AND search-condition 
| search-condition OR search-condition 
| NOT search-condition 
| ( search-condition ) 
| predicate 

db-name ::= user-defined-name 
view-name ::= user-defined-name 
join-definition ::= table-reference [ join-type ] JOIN table-reference ON search-
condition 
| table-reference CROSS JOIN table-reference 
| outer-join-definition 

join-type ::= INNER | LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL 
[ OUTER ] 
table-subquery ::= query-specification [ [ UNION [ ALL ] 
query-specification ]... ] 
subquery-expression ::= ( query-specification )

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, Pervasive PSQL 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:


Date	Decimal	Money	Numeric
NumericSA	NumericSTS	Timestamp

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:

0 for Binary types

Empty string from string and BLOB types (legacy types LVAR and NOTE)

Note
The maximum length for a single literal string is 15000 bytes. If you need to enter data longer than this, see Long Data for a useful tip.

FROM Clause

Some confusion may arise pertaining to the optional FROM clause and references to the table being updated (referred to as the "update table"). If the update table occurs in the FROM clause, then one of the occurrences is the same instance of the table being updated.

For example, in the statement UPDATE t1 SET c1 = 1 FROM t1, t2 WHERE t1.c2 = t2.c2, the t1 immediately after UPDATE is the same instance of table t1 as the t1 after FROM. Therefore, the statement is identical to UPDATE t1 SET c1 = 1 FROM t2 WHERE t1.c2 = t2.c2.

If the update table occurs in the FROM clause multiple times, one occurrence must be identified as the same instance as the update table. The FROM clause reference that is identified as the same instance as the update table is the one that does not have a specified alias.

Therefore, the statement UPDATE t1 SET t1.c1 = 1 FROM t1 a, t1 b WHERE a.c2 = b.c2 is invalid because both instances of t1 in the FROM clause contain an alias. The following version is valid: UPDATE t1 SET t1.c1 = 1 FROM t1, t1 b WHERE t1.c2 = b.c2.

The following conditions apply to the FROM clause:

If the UPDATE statement contains an optional FROM clause, the table reference prior to the FROM clause cannot have an alias specified. For example, UPDATE t1 a SET a.c1 = 1 FROM t2 WHERE a.c2 = t2.c2 returns the following error:

SQL_ERROR (-1) 
SQLSTATE of "37000" 
"[Pervasive][ODBC Client Interface][LNA][Pervasive] 
[ODBC Engine Interface]Table alias not allowed in 
UPDATE/DELETE statement with optional FROM." 
 
A valid version of the statement is UPDATE t1 SET t1.c1 = 1 FROM t2 WHERE t1.c2 = t2.c2 or UPDATE t1 SET t1.c1 = 1 FROM t1 a, t2 WHERE a.c2 = t2.c2.

If more than one reference to the update table appears in the FROM clause, then only one of the references can have a specified alias. For example, UPDATE t1 SET t1.c1 = 1 FROM t1 a, t1 b WHERE a.c2 = b.c2 returns the following error:

SQL_ERROR (-1) 
SQLSTATE of "37000" and 
"[Pervasive][ODBC Client Interface][LNA][Pervasive] 
[ODBC Engine Interface] The table t1 is 
ambiguous." 
 
In the erroneous statement, assume that you want table "t1" with alias "a" to be the same instance of the update table. A valid version of the statement would then be UPDATE t1 SET t1.c1 = 1 FROM t1, t1 b WHERE t1.c2 = b.c2.

The FROM clause is supported in an UPDATE statement only at the session level. The FROM clause is not supported if the UPDATE statement occurs within a stored procedure.

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)

The following example creates table t1 and t2 and populates them with data. The UPDATE statement uses a FROM clause to . The SELECT

DROP table t1 
CREATE table t1 (c1 integer, c2 integer) 
INSERT INTO t1 VALUES (0, 10) 
INSERT INTO t1 VALUES (0, 10) 
INSERT INTO t1 VALUES (2, 20) 
INSERT INTO t1 VALUES (2, 20) 
DROP table t2 
CREATE table t2 (c1 integer, c2 integer) 
INSERT INTO t2 VALUES (2, 20) 
INSERT INTO t2 VALUES (2, 20) 
INSERT INTO t2 VALUES (3, 30) 
INSERT INTO t2 VALUES (3, 30) 
UPDATE t1 SET t1.c1 = 1 FROM t2 WHERE t1.c2 = t2.c2 
SELECT * FROM t1

UPDATE

Syntax

Remarks

FROM Clause

Examples

See Also