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);