[sqlite] WITH () AS (SELECT ) help

[R.Smith]

On 2015-08-26 05:12 AM, jose isaias cabrera wrote:
> Greetings, perhaps this is not even possible...
>
> But I am trying to do something like this,
>
> 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.  Is this possible?  Thanks.

Expanding on what J. Decker already noted, if perhaps you do this to add 
multiple fields, this method would be a bit cumbersome. I am assuming 
you dynamically build the query, could you not just do this (which will 
have the exact same effect):

UPDATE LSOpenProjects SET XtraB = '2015-08-25 11:11:11' WHERE pmuk = 'first, 
last';


If not, this is a cleaner method I would use which allows multiple updates:

WITH EmailData (name,contact,dstamp) AS
(
   SELECT
  'last, first',
  'first.last at xerox.com',
  '2015-08-25 11:11:11'
UNION ALL
   SELECT
  'last2, first2',
  'first2.last2 at xerox.com',
  '2015-08-25 11:11:11'
)
UPDATE LSOpenProjects SET
        XtraB = (SELECT dstamp FROM EmailData WHERE EmailData.name = pmuk)
  WHERE pmuk IN (SELECT name FROM EmailData);