Professional Web Applications Themes

Multiple updates from a multiple select statement - MySQL

elyob wrote:  Use a JOIN instead of a subquery....

  1. #1

    Default Re: Multiple updates from a multiple select statement

    elyob wrote: 

    Use a JOIN instead of a subquery.


    Paul Guest

  2. #2

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 00:12, "Paul Lautman" <com> wrote: 



    >
    > Use a JOIN instead of a subquery.[/ref]

    Thanks, will try that. Have tried this with no luck ..

    UPDATE master_copy a set pt = GeomFromText('POINT(b.lat,b.lng)') LEFT
    JOIN master_copy b ON a.id=b.id;

    elyob Guest

  3. #3

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 01:51, "elyob" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Thanks, will try that. Have tried this with no luck ..
    >
    > UPDATE master_copy a set pt = GeomFromText('POINT(b.lat,b.lng)') LEFT
    > JOIN master_copy b ON a.id=b.id;[/ref]

    Sheesh, how many times do I have to write this!
    "Have tried this with no luck .."
    Just how much does that statement help us to help you!
    Does it mean there's a syntax error?
    Does it mean the wrong values are entered?

    Do you see what I'm getting at?

    BTW, it looks to me that the correct syntax for POINT() is to have
    space separated values.

    Captain Guest

  4. #4

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 09:35, "Captain Paralytic" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Sheesh, how many times do I have to write this!
    > "Have tried this with no luck .."
    > Just how much does that statement help us to help you!
    > Does it mean there's a syntax error?
    > Does it mean the wrong values are entered?
    >
    > Do you see what I'm getting at?
    >
    > BTW, it looks to me that the correct syntax for POINT() is to have
    > space separated values.[/ref]

    You're right, sorry. I did think of posting it, although I thought it
    was a fairly bland message I was getting back. I edited to try without
    the comma in the POINT() and get the same error.

    Error Code : 1064
    You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use
    near 'LEFT JOIN master_copy b ON a.id=b.id' at line 1
    (0 ms taken)

    Now that it's not middle of the night, I'll continue to have a look
    without a half shutdown brain.


    elyob Guest

  5. #5

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 11:37, "elyob" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > You're right, sorry. I did think of posting it, although I thought it
    > was a fairly bland message I was getting back. I edited to try without
    > the comma in the POINT() and get the same error.
    >
    > Error Code : 1064
    > You have an error in your SQL syntax; check the manual that
    > corresponds to your MySQL server version for the right syntax to use
    > near 'LEFT JOIN master_copy b ON a.id=b.id' at line 1
    > (0 ms taken)
    >
    > Now that it's not middle of the night, I'll continue to have a look
    > without a half shutdown brain.- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Now I'm going to let you into a secret, but don't tell anyone else.

    There's a special resource that us programmers use and it's called
    "THE MANUAL". You'd be amazed at what you can find in there!
    Now as long as no one else is looking, I'm going to tell you how to
    find it and how to use it.
    You see nowadays, lots of people are making use of another special
    secret resource known as Google.
    I just put:
    mysql update join
    into this google thing and the first hit was a link to the mysql
    manual pages for UPDATE SYNTAX.

    However I didn't even have to go into the manual as the summary text
    that google displayed said:

    MySQL uses Watcom (Oracle) syntax for UPDATE, so it's possible to
    write something like: update Table1 t1 join Table2 t2 on
    t1.ID=t2.t1ID

    So, now you are in the secret club, you too can use both Google and
    "THE MANUAL", but be carefull who you tell about it!

    Captain Guest

  6. #6

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 12:06, "Captain Paralytic" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > Now I'm going to let you into a secret, but don't tell anyone else.
    >
    > There's a special resource that us programmers use and it's called
    > "THE MANUAL". You'd be amazed at what you can find in there!
    > Now as long as no one else is looking, I'm going to tell you how to
    > find it and how to use it.
    > You see nowadays, lots of people are making use of another special
    > secret resource known as Google.
    > I just put:
    > mysql update join
    > into this google thing and the first hit was a link to the mysql
    > manual pages for UPDATE SYNTAX.
    >
    > However I didn't even have to go into the manual as the summary text
    > that google displayed said:
    >
    > MySQL uses Watcom (Oracle) syntax for UPDATE, so it's possible to
    > write something like: update Table1 t1 join Table2 t2 on
    > t1.ID=t2.t1ID
    >
    > So, now you are in the secret club, you too can use both Google and
    > "THE MANUAL", but be carefull who you tell about it![/ref]

    Well, firstly, thanks for replying.
    Secondly, can you try and be less facetious in future?
    Thirdly, this is a discussion group, I have posted *after* having read
    up and am asking for some extra pointers.
    Fourthly, I have said I was going to do more research, as it's no
    longer the middle of the night and I'm no longer half asleep.
    Fifth ... your example is joining two tables, nothing to do with the
    update I wish to do. Certainly a JOIN was the way I was pointed, but
    if you have nothing better to do with your time than police usenet
    groups ... well ...


    elyob Guest

  7. #7

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 12:16, "elyob" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]





    >
    > Well, firstly, thanks for replying.
    > Secondly, can you try and be less facetious in future?
    > Thirdly, this is a discussion group, I have posted *after* having read
    > up and am asking for some extra pointers.
    > Fourthly, I have said I was going to do more research, as it's no
    > longer the middle of the night and I'm no longer half asleep.
    > Fifth ... your example is joining two tables, nothing to do with the
    > update I wish to do. Certainly a JOIN was the way I was pointed, but
    > if you have nothing better to do with your time than police usenet
    > groups ... well ...- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Calm down now, can't you see from the way it was written that it was
    humourous?
    Also I have lots of things to do with my time, but if you look through
    the archives, you'll note that there is a core of about 5-10 of us who
    answer the majority of the questions one this group. If you don't want
    our help then bugger off and do it yourself. You will note that I am
    the only person who has been assisting with this thread, so it looks
    like if you're going to keep up this attitude that's what you're going
    to end up having to do.

    If you had read up you'd have seen that yout UPDATE/JOIN syntax was
    wrong, as a simple google search revealed.

    Now, I don't understand the comment "your example is joining two
    tables, nothing to do with the
    update I wish to do"

    You wish to update one table using data from another table. These 2
    tables are linked by a common field called id.

    This operation is called a JOIN. Thus, the update that you wish to do
    has EVERYTHING to do with joining 2 tables. If you're too dumb to
    figure that out then maybe you should leave this job to someone with
    the necessary brain power.

    Goodbye

    Captain Guest

  8. #8

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 12:54, "Captain Paralytic" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Calm down now, can't you see from the way it was written that it was
    > humourous?
    > Also I have lots of things to do with my time, but if you look through
    > the archives, you'll note that there is a core of about 5-10 of us who
    > answer the majority of the questions one this group. If you don't want
    > our help then bugger off and do it yourself. You will note that I am
    > the only person who has been assisting with this thread, so it looks
    > like if you're going to keep up this attitude that's what you're going
    > to end up having to do.
    >
    > If you had read up you'd have seen that yout UPDATE/JOIN syntax was
    > wrong, as a simple google search revealed.
    >
    > Now, I don't understand the comment "your example is joining two
    > tables, nothing to do with the
    > update I wish to do"
    >
    > You wish to update one table using data from another table. These 2
    > tables are linked by a common field called id.
    >
    > This operation is called a JOIN. Thus, the update that you wish to do
    > has EVERYTHING to do with joining 2 tables. If you're too dumb to
    > figure that out then maybe you should leave this job to someone with
    > the necessary brain power.
    >
    > Goodbye[/ref]

    You should know by now that what someone intends to be humorous in a
    non-expressional format can often be misinterpreted.

    I wish to update information on the same table as the select. I
    already have been told it is a join, Paul Lautman's three word answer
    was very helpful, and I thought I'd pop an update and ask if there was
    an obvious mistake with the statement.

    I'm sure you and others are very helpful, however you're replies
    probably took you more time "being humorous" than it would have to
    have solved my query. As I clearly stated, I was posting in the middle
    of the night and I would prefer to be RTFM than replying to you which
    just takes the thread off on a tangent. What really gets me, is that
    when someone is searching for a solution to this in the future will
    have to sift through all this rubbish.

    As I said, Paul was more succinct, less abusive and just as factual in
    just a few words.


    elyob Guest

  9. #9

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 13:11, "elyob" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]






    >
    > You should know by now that what someone intends to be humorous in a
    > non-expressional format can often be misinterpreted.
    >
    > I wish to update information on the same table as the select. I
    > already have been told it is a join, Paul Lautman's three word answer
    > was very helpful, and I thought I'd pop an update and ask if there was
    > an obvious mistake with the statement.
    >
    > I'm sure you and others are very helpful, however you're replies
    > probably took you more time "being humorous" than it would have to
    > have solved my query. As I clearly stated, I was posting in the middle
    > of the night and I would prefer to be RTFM than replying to you which
    > just takes the thread off on a tangent. What really gets me, is that
    > when someone is searching for a solution to this in the future will
    > have to sift through all this rubbish.
    >
    > As I said, Paul was more succinct, less abusive and just as factual in
    > just a few words.- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Check out the email addresses:

    Paul Lautman = paul.laut......
    Captain Paralytic = paul_laut......

    therefore

    Captain Paralytic = Paul Lautman

    As I was saying "I am the only person who has been assisting with this
    thread"

    I hadn't noticed (and neither had my alter ego :-) that both table are
    the same.
    In that case all you should need is:
    update master_copy
    set pt = GeomFromText('POINT(lat lng)')

    Captain Guest

  10. #10

    Default Re: Multiple updates from a multiple select statement

    On Mar 1, 1:11 pm, "elyob" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]






    >
    > You should know by now that what someone intends to be humorous in a
    > non-expressional format can often be misinterpreted.
    >
    > I wish to update information on the same table as the select. I
    > already have been told it is a join, Paul Lautman's three word answer
    > was very helpful, and I thought I'd pop an update and ask if there was
    > an obvious mistake with the statement.
    >
    > I'm sure you and others are very helpful, however you're replies
    > probably took you more time "being humorous" than it would have to
    > have solved my query. As I clearly stated, I was posting in the middle
    > of the night and I would prefer to be RTFM than replying to you which
    > just takes the thread off on a tangent. What really gets me, is that
    > when someone is searching for a solution to this in the future will
    > have to sift through all this rubbish.
    >
    > As I said, Paul was more succinct, less abusive and just as factual in
    > just a few words.[/ref]

    Ah, Jekyll and Hyde I presume

    strawberry Guest

  11. #11

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 14:01, "strawberry" <com> wrote:
     

    Could be, it depends one where I am posting from. I only keep the
    otion in one place.

    Captain Guest

  12. #12

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 13:59, "Captain Paralytic" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]





    >
    > Check out the email addresses:
    >
    > Paul Lautman = paul.laut......
    > Captain Paralytic = paul_laut......
    >
    > therefore
    >
    > Captain Paralytic = Paul Lautman
    >
    > As I was saying "I am the only person who has been assisting with this
    > thread"
    >
    > I hadn't noticed (and neither had my alter ego :-) that both table are
    > the same.
    > In that case all you should need is:
    > update master_copy
    > set pt = GeomFromText('POINT(lat lng)')[/ref]

    Hi Paul! Didn't check the email addresses, hardly ever do!

    I've been looking into it today, and I could cleanly do with a script,
    but the challenge to me was to do purely with SQL. Yes, it turns out
    there is no need for a join at all, and I can just do a straight call.
    However I have had some unusual results ...

    If I try the above :

    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 20 Changed: 0 Warnings: 0

    I've been trying to CONCAT the lat & lng and then place into the
    POINT, but haven't succeeded there either.

    Why would it match 20 rows, but not change them without failing in
    error?

    Welcome back Paul ;)

    elyob Guest

  13. #13

    Default Re: Multiple updates from a multiple select statement

    On 1 Mar, 19:29, "elyob" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]






    >
    > Hi Paul! Didn't check the email addresses, hardly ever do!
    >
    > I've been looking into it today, and I could cleanly do with a script,
    > but the challenge to me was to do purely with SQL. Yes, it turns out
    > there is no need for a join at all, and I can just do a straight call.
    > However I have had some unusual results ...
    >
    > If I try the above :
    >
    > Query OK, 0 rows affected (0.00 sec)
    > Rows matched: 20 Changed: 0 Warnings: 0
    >
    > I've been trying to CONCAT the lat & lng and then place into the
    > POINT, but haven't succeeded there either.
    >
    > Why would it match 20 rows, but not change them without failing in
    > error?
    >
    > Welcome back Paul ;)- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    The match is how many rows matched the criteria in the WHERE clause
    (if there is no WHERE clause then it'll be all the rowsin the table).
    A change is as it says which rows had data changed. If the data was
    already the same as what it would have been updated with, then nothing
    has actually changed.

    Captain Guest

Similar Threads

  1. Replies: 8
    Last Post: February 28th, 03:45 AM
  2. Coursebulider Multiple Select-Multiple Feedback
    By allankobernick in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: October 28th, 01:04 PM
  3. Replies: 0
    Last Post: September 24th, 03:24 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM

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