[sqlite] WITH () AS (SELECT ) help

[Clemens Ladisch]

jose isaias cabrera wrote:
> WITH EmailData (name,contact,dstamp) AS
> (
>   SELECT
>  'last, first',
>  'first.last at xerox.com',
>  '2015-08-25 11:11:11'
> )
> UPDATE LSOpenProjects SET XtraB = EmailData.dstamp, pmuk = EmailData.contact 
> WHERE pmuk = EmailData.name;
>
> But, it's not working.

A CTE behaves like a table or a view.  So if you had created a view
named "EmailData", your UPDATE statement would not work either because
"EmailData" is never mentioned in the UPDATE or a FROM clause.

Regardless of whether you're using WITH or not, you need to use
subqueries to get at the values:

  WITH ...
  UPDATE LSOpenProjects
  SET XtraB = (SELECT dstamp  FROM EmailData),
      pmuk  = (SELECT contact FROM EmailData)
  WHERE pmuk = (SELECT name FROM EmailData);


Regards,
Clemens