I was trying to reduce a set of queries and ran into this:


insert into forums (forum_reply_id, forum_dev_id, forum_subject,
forum_message) values (0, 1, (select forum_subject from forums where
forum_id=3 ), "I figured this one needed a reply too.")

yields:

error: You can't specify target table from 'forums' for update in from
clause

I read somewhere online when they referenced 4.x that said you cannot do
subqueries in an update. Is this true of 5.x ?

Thanks

Steffan

---------------------------------------------------------------
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
Steffan@ExecuChoice.net Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
Lasso Partner Alliance Member
---------------------------------------------------------------

On Sun, July 15, 2007 03:53, Steffan A. Cline wrote:
> I was trying to reduce a set of queries and ran into this:
>
>
> insert into forums (forum_reply_id, forum_dev_id, forum_subject,
> forum_message) values (0, 1, (select forum_subject from forums where
> forum_id=3 ), "I figured this one needed a reply too.")

Your subselect "could" return more than one row, and because of that,
can't be used in a direct insert/update.

>
> yields:
>
> error: You can't specify target table from 'forums' for update in from
> clause
>
> I read somewhere online when they referenced 4.x that said you cannot do
> subqueries in an update. Is this true of 5.x ?
>
> Thanks
>
> Steffan
>
> ---------------------------------------------------------------
> T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
> Steffan A. Cline
> Steffan@ExecuChoice.net Phoenix, Az
> http://www.ExecuChoice.net USA
> AIM : SteffanC ICQ : 57234309
> Lasso Partner Alliance Member
> ---------------------------------------------------------------
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mogens@fumlersoft.dk
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>


--
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Mogens ,

forum_id is a primary auto increment key therefore it's unlikely that there
would be a dupe. I can see there being an error if there is a dupe but in
this case there is not.


Is it really necessary to do this:

set @x = (select forum_subject from forums where forum_id=3 );

insert into forums
(project_id, forum_reply_id, forum_dev_id, forum_subject, forum_message)
values
(42, 6, 1, @x, "I figured this one needed a reply too.");

?



Thanks

Steffan

---------------------------------------------------------------
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
Steffan@ExecuChoice.net Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
Lasso Partner Alliance Member
---------------------------------------------------------------



> From: Mogens Melander <mogens@fumlersoft.dk>
> Date: Sun, 15 Jul 2007 04:02:18 +0200 (CEST)
> To: "Steffan A. Cline" <steffan@hldns.com>
> Cc: <mysql@lists.mysql.com>
> Subject: Re: New error to me...
>
>
> On Sun, July 15, 2007 03:53, Steffan A. Cline wrote:
>> I was trying to reduce a set of queries and ran into this:
>>
>>
>> insert into forums (forum_reply_id, forum_dev_id, forum_subject,
>> forum_message) values (0, 1, (select forum_subject from forums where
>> forum_id=3 ), "I figured this one needed a reply too.")
>
> Your subselect "could" return more than one row, and because of that,
> can't be used in a direct insert/update.
>
>>
>> yields:
>>
>> error: You can't specify target table from 'forums' for update in from
>> clause
>>
>> I read somewhere online when they referenced 4.x that said you cannot do
>> subqueries in an update. Is this true of 5.x ?
>>
>> Thanks
>>
>> Steffan
>>
>> ---------------------------------------------------------------
>> T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
>> Steffan A. Cline
>> Steffan@ExecuChoice.net Phoenix, Az
>> http://www.ExecuChoice.net USA
>> AIM : SteffanC ICQ : 57234309
>> Lasso Partner Alliance Member
>> ---------------------------------------------------------------
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mogens@fumlersoft.dk
>>
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is
>> believed to be clean.
>>
>
>
> --
> Later
>
> Mogens Melander
> +45 40 85 71 38
> +66 870 133 224
>
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=steffan@hldns.com
>

At 6:53 PM -0700 7/14/07, Steffan A. Cline wrote:
>I was trying to reduce a set of queries and ran into this:
>
>
>insert into forums (forum_reply_id, forum_dev_id, forum_subject,
>forum_message) values (0, 1, (select forum_subject from forums where
>forum_id=3 ), "I figured this one needed a reply too.")
>
>yields:
>
>error: You can't specify target table from 'forums' for update in from
>clause
>
>I read somewhere online when they referenced 4.x that said you cannot do
>subqueries in an update. Is this true of 5.x ?

You can do subqueries *unless* you're selecting from the same table
that you're inserting into.

http://dev.mysql.com/doc/refman/5.0/...rt-select.html

(You're not quite using INSERT INTO ... SELECT syntax, but what
you're doing appears to be effectively equivalent.)

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com