DML UPDATE


UPDATE statement is used to update the rows which are already existed in the table.UPDATE can be single row update or mass update (more than one row) based on the condition provided in WHERE clause.

User should have UPDATE privilege on the table to perform UPDATE operation. Updating row in view can update the row in base table as well.Once the UPDATE executed successfully, the rows updated count will be stored in SQLERRD (3).

Syntax -


UPDATE table-name
SET COLUMN1 = column1-value,	 
    COLUMN2 = column2-value,
                 .	
    COLUMNn = columnn-value,	
    WHERE conditions  

If the UPDATE has any error during the updating process, SQLCODE and SQLSTATE will have the error details. If no rows existed in the table which are satisfies where condition, then the UPDATE statement will return SQLCODE +100 and update will be skipped. If the WHERE clause ignored in the UPDATE, it will update all the rows of the table with the values specified for the columns.

How the UPDATE statement used in the Program -


Updating of Data can be done in the program by using Host variables or by directly setting the values to the columns. The steps to use UPDATE in the program is:

  • Include the host variables copybook or declare the host variables in working storage section.
  • Fill the data to the HOST-variables.
  • Non nullable columns data should be provided, i.e. if no data available to fill the Host variables, initialization of Host variables required at least.
  • If need to UPDATE the NULL value, then NULL indicator field needs be initialized with -1.
  • Execute the UPDATE query to insert the data like below.

Syntax -


EXEC SQL
	UPDATE Table-name 
	SET      Column-1	= 	: Host-variable-1,
		Column-2	=	: Host-variable-2,
		… ,
		Column-n	=	: Host-variable-n
	WHERE Condition
END-EXEC.
Note!   If the UPDATE statement executed successfully, SQLERRD(3) is set to the number of rows got updated.

Error handling -


If UPDATE query successfully updates the data into table, then UPDATE query will return +000 (SUCCESSFUL) as SQLCODE. If UPDATE query not found any rows to update, then UPDATE query will return +100 (NOT FOUND) as SQLCODE.

If any NULL value trying to insert through UPDATE query and NULL indicator with value ‘-1’ not coded along with host-variable, then UPDATE query abends with -305 (NULL value exception) as SQLCODE.

If the user has no access to table and trying to perform UPDATE query on the table , then UPDATE query abends with -922 (Authorization failure) as SQLCODE.