Updating a select statement.

Hello,

So I'm trying to update a select statement. Here's my query



UPDATE
(SELECT a.id, a.colonize, b.*
FROM planets AS a, minerals AS b
WHERE a.id = b.planid
AND a.colonize IS false) AS table1

SET n = n + ( nC * ( 5 /3600 ) /100 ) ,
t = t + ( tC * ( 5 /3600 ) /100 ) ,
d = d + ( dC * ( 5 /3600 ) /100 ) ,
m = m + ( mC * ( 5 /3600 ) /100 )



This is the error I get
#1288 - The target table table1 of the UPDATE is not updatable.

How do i go about making that Select statement updatable? Should I use
Join will that help?

Ryan Knopp wrote:
> UPDATE
> (SELECT a.id, a.colonize, b.*
> FROM planets AS a, minerals AS b
> WHERE a.id = b.planid
> AND a.colonize IS false) AS table1
>
> SET n = n + ( nC * ( 5 /3600 ) /100 ) ,
> t = t + ( tC * ( 5 /3600 ) /100 ) ,
> d = d + ( dC * ( 5 /3600 ) /100 ) ,
> m = m + ( mC * ( 5 /3600 ) /100 )
>
> This is the error I get
> #1288 - The target table table1 of the UPDATE is not updatable.

I don't think that you can update a Select? Maybe you can:

UPDATE a,b
SET n = n + ( nC * ( 5 /3600 ) /100 ) ,
t = t + ( tC * ( 5 /3600 ) /100 ) ,
d = d + ( dC * ( 5 /3600 ) /100 ) ,
m = m + ( mC * ( 5 /3600 ) /100 )
WHERE a.id = b.planid AND a.colonize IS false

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org

Nicholas Sherlock wrote:
> Ryan Knopp wrote:
>> UPDATE
>> (SELECT a.id, a.colonize, b.*
>> FROM planets AS a, minerals AS b
>> WHERE a.id = b.planid
>> AND a.colonize IS false) AS table1
>>
>> SET n = n + ( nC * ( 5 /3600 ) /100 ) ,
>> t = t + ( tC * ( 5 /3600 ) /100 ) ,
>> d = d + ( dC * ( 5 /3600 ) /100 ) ,
>> m = m + ( mC * ( 5 /3600 ) /100 )
>>
>> This is the error I get
>> #1288 - The target table table1 of the UPDATE is not updatable.
>
> I don't think that you can update a Select? Maybe you can:
>
> UPDATE a,b
> SET n = n + ( nC * ( 5 /3600 ) /100 ) ,
> t = t + ( tC * ( 5 /3600 ) /100 ) ,
> d = d + ( dC * ( 5 /3600 ) /100 ) ,
> m = m + ( mC * ( 5 /3600 ) /100 )
> WHERE a.id = b.planid AND a.colonize IS false
>
> Cheers,
> Nicholas Sherlock
>
Thanks, That works!!!!