WITH () AS (SELECT ) help

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

WITH () AS (SELECT ) help

jose isaias cabrera

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',
 '[hidden email]',
 '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.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WITH () AS (SELECT ) help

Clemens Ladisch
jose isaias cabrera wrote:

> WITH EmailData (name,contact,dstamp) AS
> (
>   SELECT
>  'last, first',
>  '[hidden email]',
>  '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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WITH () AS (SELECT ) help

R Smith
In reply to this post by jose isaias cabrera


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',
>   '[hidden email]',
>   '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',
  '[hidden email]',
  '2015-08-25 11:11:11'
UNION ALL
   SELECT
  'last2, first2',
  '[hidden email]',
  '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);



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users