Professional Web Applications Themes

Updating a select statement. - MySQL

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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Updating a select statement.

    Ryan Knopp wrote: 

    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 Guest

  3. #3

    Default Re: Updating a select statement.

    Nicholas Sherlock wrote: 
    >
    > 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
    >[/ref]
    Thanks, That works!!!!
    Ryan Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  3. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  4. Need help with select statement
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 04:53 AM
  5. Need some help on a SELECT statement
    By Sydney in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 16th, 01:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139