******************************************************************************************************

  SQL commands to change table data

******************************************************************************************************

The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.   The SET statement is the new data and the where statement is the old data.

UPDATE "tablename"

SET "columnname" = "newvalue"[,"nextcolumn" = "newvalue2"...]

WHERE "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"];

[] = optional

Example of completely changing the value in a field -- where entire field is a certain character string:

UPDATE DBA.sales_order

SET fin_code_id = 'r2'

WHERE fin_code_id = 'r1'

Example of partially changing the first four characters of a field -- and leaving the remainder of the field the same.  Use in conjunction with SUBSTR( expression, start, length) expression. 

Let's say we want to change the year portion from 1993 to 1992 of the field order_date:

1993-01-02

1993-01-04

1993-01-06

Firstpart = SUBSTR(order_date, 1, 4)

Secondpart = SUBSTR(order_date, 5)    ' The length is optional -- will simply take the rest of the expression (very nice!)

Entire expression would be:

UPDATE DBA.sales_order                       ' The table were updating

SET order_date = '1992' || SUBSTR(order_date,5)                 ' The field & replacement value (note the partial string replacement)

WHERE SUBSTR(order_date, 1, 4) = '1993'                           ' Our search criteria -- looking for the first four characters equal to '1993'

More Examples (note: in examples below we can be looking at other fields in same table -- or even other tables!:

UPDATE phone_book

SET area_code = 623

WHERE prefix = 979;

UPDATE phone_book

SET last_name = 'Smith', prefix=555, suffix=9292

WHERE last_name = 'Jones';

UPDATE employee

SET age = age+1

WHERE first_name='Mary' and last_name='Williams';