Re: referencing other INSERT VALUES columns inside the insert

Geoff Winkless wrote:
> I know that this is something that can't be done...
> 
> CREATE TABLE test (c1 int default 0, c2 int default 0, c3 int default 0);
> INSERT INTO test (c1, c2, c3) VALUES (3, 7, c1 * c2);
> 
> Is there a known trick to work around it (so that the values inserted into c1 and c2 is referenced
> back to c3), other than (obviously!) copying the literal values into the VALUES string?
> 
> To be clear, the SQL is generated dynamically based on data, so I can't just create a view for the
> calculated column (it won't always be calculated!).

What about something along these lines:

INSERT INTO test (c1, c2, c3)
   (WITH fixed(x1, x2) AS (VALUES (3, 7))
    SELECT x1, x2, x1 * x2 FROM fixed);