I need to create a UPDATE query as a subquery of a SELECT one (after
the where keyword)

Something like:
SELECT x FROM u WHERE k=i AND y IN (UPDATE o SET p=i WHERE r=u) AND y
< 4
(This is a wrong one)

Even it doesn't make any sense, I need the update query to be
executed.

Thanks in advance,
Alfio

Demonstration of your efforts at constructing the query for yourself are ap=
preciated. However, if you want help I suggest you provide a representative=
 data set together with an illustration (in something approaching plain Eng=
lish) of what it is you're trying to achieve.

On 3/25/2011 2:47 AM, Alfio Emanuele Fresta wrote:
> I need to create a UPDATE query as a subquery of a SELECT one (after
> the where keyword)
>
> Something like:
> SELECT x FROM u WHERE k=i AND y IN (UPDATE o SET p=i WHERE r=u) AND y
> <  4
> (This is a wrong one)
>
> Even it doesn't make any sense, I need the update query to be
> executed.
>
> Thanks in advance,
> Alfio

SQL doesn't allow an UPDATE as a subquery of a SELECT statement.

Maybe rather then telling us what you need, you should tell us what 
you're trying to do. Then maybe we can give you suggestions as to what 
can work.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> SQL doesn't allow an UPDATE as a subquery of a SELECT statement.

Actually ANSI SQL does allow that:
SELECT * FROM OLD TABLE(UPDATE T SET c1 = 5)

What mySQL allows is another matter..

Cheers
Serge

-- 
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog:    tinyurl.com/SQLTips4DB2
Wiki:    tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

On 3/27/2011 4:22 PM, Serge Rielau wrote:
>> SQL doesn't allow an UPDATE as a subquery of a SELECT statement.
>
> Actually ANSI SQL does allow that:
> SELECT * FROM OLD TABLE(UPDATE T SET c1 = 5)
>
> What mySQL allows is another matter..
>
> Cheers
> Serge
>

Interesting, Serge.  I haven't seen that in the SQL specs.  Do you know 
which version specified it?

Thanks for the info.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> Interesting, Serge. I haven't seen that in the SQL specs. Do you know
> which version specified it?
Should be in SQL:2008
Checking....

We first published the extension at VLDB 2004:
http://portal.acm.org/citation.cfm?id=1316774

Cheers
Serge

-- 
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog:    tinyurl.com/SQLTips4DB2
Wiki:    tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

On 3/28/2011 7:58 AM, Serge Rielau wrote:
>> Interesting, Serge. I haven't seen that in the SQL specs. Do you know
>> which version specified it?
> Should be in SQL:2008
> Checking....
>
> We first published the extension at VLDB 2004:
> http://portal.acm.org/citation.cfm?id=1316774
>
> Cheers
> Serge
>

Interesting, and I can see where there could be performance 
improvements.  But which version of the ANSI standard picked it up?

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 3/28/2011 9:50 AM, Jerry Stuckle wrote:
> On 3/28/2011 7:58 AM, Serge Rielau wrote:
>>> Interesting, Serge. I haven't seen that in the SQL specs. Do you know
>>> which version specified it?
>> Should be in SQL:2008
>> Checking....
>>
>> We first published the extension at VLDB 2004:
>> http://portal.acm.org/citation.cfm?id=1316774
>>
>> Cheers
>> Serge
>>
>
> Interesting, and I can see where there could be performance
> improvements. But which version of the ANSI standard picked it up?
>
I have bugged our standards reps and here is the feedback I got:

"Yes, SELECT FROM INSERT has indeed made it into ANSI as well as ISO SQL 
standards, but it is not yet visible to the wider world since it was 
introduced after the current version of SQL standard, SQL-2008, came 
out. It will become visible when the next version gets published, which 
is expected either late this year or early next year. It normally takes 
3 to 4 year cycle for a new version of SQL standard to come out."

If you are interested I can send you a PDF of the submission.

Cheers
Serge

-- 
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog:    tinyurl.com/SQLTips4DB2
Wiki:    tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

On 4/4/2011 6:59 PM, Serge Rielau wrote:
> On 3/28/2011 9:50 AM, Jerry Stuckle wrote:
>> On 3/28/2011 7:58 AM, Serge Rielau wrote:
>>>> Interesting, Serge. I haven't seen that in the SQL specs. Do you know
>>>> which version specified it?
>>> Should be in SQL:2008
>>> Checking....
>>>
>>> We first published the extension at VLDB 2004:
>>> http://portal.acm.org/citation.cfm?id=1316774
>>>
>>> Cheers
>>> Serge
>>>
>>
>> Interesting, and I can see where there could be performance
>> improvements. But which version of the ANSI standard picked it up?
>>
> I have bugged our standards reps and here is the feedback I got:
>
> "Yes, SELECT FROM INSERT has indeed made it into ANSI as well as ISO SQL
> standards, but it is not yet visible to the wider world since it was
> introduced after the current version of SQL standard, SQL-2008, came
> out. It will become visible when the next version gets published, which
> is expected either late this year or early next year. It normally takes
> 3 to 4 year cycle for a new version of SQL standard to come out."
>
> If you are interested I can send you a PDF of the submission.
>
> Cheers
> Serge
>

No, that's fine, Serge.  And that makes sense - I didn't think it was in 
the current standard.

I appreciate the info!

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================