Caché SQL Reference
[Back] [Next]
Go to:

An INSERT/UPDATE clause that specifies data values for use in fields.
     VALUES (value1{,valuen})
field A field name or a comma-separated list of field names.
value A value or comma-separated list of values. Each value is assigned to the corresponding field.
The VALUES clause is used in an INSERT or UPDATE statement to specify the data values to insert into the fields. Typically:
The elements in the VALUES clause correspond in sequence to the fields specified after the table name. Note if there is only one value element specified in the VALUES clause, it is not necessary to enclose the element in parentheses.
The following embedded SQL example shows an INSERT statement that adds a single row to the "Employee" table:
   &sql(INSERT INTO Employee (Name,SocSec,Telephone)
   &sql(INSERT INTO Employee (Name,SocSec,Telephone)
        VALUES ('Boswell',333448888,'546-7989'))
INSERT and UPDATE queries can use a VALUES clause without requiring you to explicitly specify a list of field names after the table name. In order to omit the list of field names after the table name, your query must meet the following two criteria:
For example, the query:
INSERT INTO Sample.Person VALUES (5,'John')
is equivalent to the query:
INSERT INTO Sample.Person (Age,Name) VALUES (5,'John')
if the table "Sample.Person" has exactly two user-defined fields.
In this example, the value 5 is assigned to the field with the lower column number, and the value "John" is assigned to the other field.
A VALUES clause can specify an element of an array, as is the following embedded SQL example:
   &sql( UPDATE Person(Tel)
        VALUES :per('tel',)
        WHERE ID = :id )
An UPDATE query can also reference an array with unspecified last subscript. Whereas INSERT uses the presence and absence of array elements to assign values and default values to a newly created row, UPDATE uses the presence of an array element to indicate that the corresponding field should be updated. For example, consider the following array for a table with six columns:
emp("profile",3,1)="1441 Main St."
emp("profile",3,2)="Cableton, IL 60433"
Column 1 is always reserved for the ID field, and is not user-specified. The inserted "Employee" row has Column 2, "Name", set to "Smith"; Column 3, "Address", set to a two-line value; Column 4, "Department", is not specified, and is thus set to the default, and Column 5, "Location", is set to NULL. The default value for "Location" is not used since the corresponding array element is defined with a null value. The array elements "7" and "next" do not correspond to column numbers in the "Employee" table, therefore the query ignores them. Here’s the UPDATE statement that uses this array:
  &sql(UPDATE Employee
       VALUES :emp('profile',)
       WHERE Employee = 379)
Given the above definitions and array values, this statement will update the values of the "Name", "Address", and "Location" fields of the "Employee" row for which Row ID = 379.
However, omitting the subscript entirely results in an SQLCODE -54 error: Array designator (last subscript omitted) expected after VALUES.
You may also use an array reference with an UPDATE query that targets multiple rows, for example:
  &sql(UPDATE Employee
       VALUES :emp('profile',)
       WHERE Type = 'PART-TIME')
A VALUES clause variable cannot use dot syntax. Therefore, the following embedded SQL example is correct:
   SET sname = state.Name
   &sql(INSERT INTO StateTbl VALUES :sname)
The following is not correct:
     &sql(INSERT INTO State VALUES :state.Name)
NULL and empty string values are different. For further details, see NULL. For backward compatibility, all empty string ('') values in older existing data are considered as NULL values. In new data, empty strings are stored in the data as $CHAR(0). Through SQL, NULL is referenced as 'NULL'. For example:
INSERT INTO Sample.Person
(SSN,Name,Home_City) VALUES ('123-45-6789','Doe,John',NULL)
Through SQL, empty string is referenced as '' (two single quotes). For example:
INSERT INTO Sample.Person
(SSN,Name,Home_City) VALUES ('123-45-6789','Doe,John','')
You cannot insert a NULL value for the ID field.
The following embedded SQL example inserts a record for “Doe,John” into the Sample.Person table. It then selects this record, and then deletes this record. A second SELECT confirms the deletion.
   SET x="Doe,John",y="123-45-6789",z="Metropolis"
   SET (a,b,c,d,e)=0
   &sql(INSERT INTO Sample.Person
   (Name,SSN,Home_City) VALUES (:x,:y,:z))
   IF SQLCODE'=0 {
     WRITE !,"INSERT Error code ",SQLCODE
     QUIT }
   &sql(SELECT Name,SSN,Home_City
        INTO :a,:b,:c
        FROM Sample.Person
        WHERE Name =:x)
   IF SQLCODE'=0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
      WRITE !,"After INSERT:"
      WRITE !,"Name=",a," SSN=",b," City=",c
      WRITE !,"SQL code=",SQLCODE," Number of rows=",%ROWCOUNT }
    &sql(DELETE FROM Sample.Person
        WHERE Name=:x)
    &sql(SELECT Name,SSN
        INTO :d,:e
        FROM Sample.Person
        WHERE Name='Doe,John')
   IF SQLCODE <0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
     WRITE !,"After DELETE:"
     WRITE !,"Name=",d," SSN=",e
     WRITE !,"SQL code=",SQLCODE," Number of rows=",%ROWCOUNT }
See Also