SWI-Prolog -- Manual

Documentation
- Reference manual
- Packages
  - Constraint Query Language A high level interface to SQL databases
    - library(cql/cql): CQL - Constraint Query Language
      - Examples
        
        Simple INSERT
        
        Simple INSERT with retrieval of identity of the inserted
        
        Simple DELETE
        
        Simple SELECT
        
        Simple UPDATE
        
        WHERE with arithmetic comparison
        
        Simple INNER JOIN
        
        Arithmetic UPDATE with an INNER JOIN and a WHERE restriction
        
        Confirm row does not exist
        
        Aggregation - Count
        
        Aggregation - Sum
        
        Aggregation - Average
        
        Maximum Value
        
        Minimum Value
        
        Aggregation requiring GROUP BY
        
        INNER JOIN with an aggregation sub-query where the sub-query is constrained by a shared variable from the main query
        
        INNER JOIN in an aggregation sub-query
        
        Negation
        
        EXISTS
        
        Left Outer Join
        
        List-based Restrictions
        
        Compile time in-list constraint
        
        Disjunction resulting in OR in WHERE clause
        
        Disjunction resulting in different joins (implemented as a SQL UNION)
        
        Disjunction resulting in different SELECT attributes (implemented as separate ODBC queries)
        
        ORDER BY
        
        DISTINCT
        
        SELECT with NOT NULL restriction
        
        First N
        
        Self JOIN
        
        Removing null comparisions
        
        Three table JOIN
        
        Three table JOIN with NOLOCK locking hint
        
        SELECT with LIKE
        
        Writing exceptions directly to the database
        
        TOP N is Parametric
        
        Using compile_time_goal/1
        
        ON
        
        Expressions In Where Restrictions
        
        Explicitly avoid the "No WHERE restriction" message
        
        HAVING
        
        INSERT and UPDATE value in-line formatting
        
        Negations in WHERE Clauses
        
        Predicate-generated Attribute Values
        
        INSERT from SELECT

1.7.45 INSERT from SELECT

INSERT from SELECT is supported:

Constant = 'MIKE',
{[],
 insert(se_lt_x1, [x_pk-Pk, a-A, b-B, c-C, d-Constant]),
 se_lt_x :: [x_pk-Pk, a-A, b-B, c-C, as(d)-Constant]}

which generates the following SQL:

INSERT INTO se_lt_x1 (x_pk, a, b, c, d)
SELECT se_lt_x_955.x_pk, se_lt_x_955.a, se_lt_x_955.b, se_lt_x_955.c, ? AS d
  FROM se_lt_x lt_x_955

Note the use of the as(d) construct in the SELECT part of the CQL to make the constant 'MIKE' appear to come from the SELECT thus setting lt_x1.d to 'MIKE' in every row inserted.