SQL Update all rows in a table

UPDATE product 
SET desc = ‘nvt’

SQL Update all rows in a table specifying the library name

UPDATE mylib/product 
SET desc = ‘nvt’

SQL Update selection of rows

UPDATE product 
SET desc = ‘nvt’ 
WHERE code = ‘E’

UPDATE product 
SET desc= ‘nvt’ 
WHERE code = ‘E’ OR code = ‘Z’

SQL Update rows with static value with selection based on conditions in the primary and secondary table

UPDATE customer
 SET jrmet1 = 'C'
 WHERE exists (SELECT * FROM account
 WHERE customer.csnum = account.csnum
 and  (csland = 'NL' or csland = 'FR')
 and  accsts= '1')

SQL Update rows based on value and existence in another table

UPDATE customer
 SET country =
 ( SELECT country_new
 FROM customer2
 WHERE ( prcsnr = xxcsnr   and
 country = country_old
 )
 )
 WHERE EXISTS
 ( SELECT country_new
 FROM customer2
 WHERE ( prcsnr = xxcsnr  and
 country = country_old
 )
 )

SQL Update field using the SUBSTRING function

Insert two extra zeros into a text string….

UPDATE acccnv 
SET atext =
 substring(atext from 1    for 1070) || ’00′ ||
 substring(atext from 1071 for 1246)

SQL Update field using the LIKE function

UPDATE customer       
SET  city = 'Cardiff '
WHERE postcode LIKE '%CF11%'

SQL Update with Find and Replace text

Translate the customer title from English to Dutch

-- Before update, name contains 'Mr Dirk van Amersfoort'

UPDATE customer
SET    name = REPLACE(name, 'Mr', 'Dhr') 
WHERE  language = 'NL'

-- Afterupdate, name contains 'Dhr Dirk van Amersfoort'

To adjust or correct spelling

-- Before update, text contains 'The color is red'
UPDATE notes
SET    text = REPLACE(text, 'color', 'colour')
-- After update, text contains 'The colour is red'