Multiple updates from a multiple select statement

On Mar 1, 1:11 pm, "elyob" <newsprof...@gmail.com> wrote:
> On 1 Mar, 12:54, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 1 Mar, 12:16, "elyob" <newsprof...@gmail.com> wrote:
>
> > > On 1 Mar, 12:06, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > On 1 Mar, 11:37, "elyob" <newsprof...@gmail.com> wrote:
>
> > > > > On 1 Mar, 09:35, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > > > On 1 Mar, 01:51, "elyob" <newsprof...@gmail.com> wrote:
>
> > > > > > > On 1 Mar, 00:12, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
> > > > > > > > elyob wrote:
> > > > > > > > > Hi, rather than writing a script, I'm looking to run through a whole
> > > > > > > > > table and update each row individually with data from each row.
>
> > > > > > > > > This is as far as I've got, and I'm stuck.
>
> > > > > > > > > update master_copy a
> > > > > > > > > set pt = GeomFromText('POINT(b.lat,b.lng)')
> > > > > > > > > where a.id in
> > > > > > > > > (select b.id, b.lat, b.lng from master_copy b);
>
> > > > > > > > > Thanks
>
> > > > > > > > Use a JOIN instead of a subquery.
>
> > > > > > > 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;
>
> > > > > > 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.
>
> > > > > 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 -
>
> > > > 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!
>
> > > 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 -
>
> > 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
>
> 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.

Ah, Jekyll and Hyde I presume

On 1 Mar, 14:01, "strawberry" <zac.ca...@gmail.com> wrote:

> Ah, Jekyll and Hyde I presume

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

On 1 Mar, 13:59, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
> On 1 Mar, 13:11, "elyob" <newsprof...@gmail.com> wrote:
>
>
>
> > On 1 Mar, 12:54, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > On 1 Mar, 12:16, "elyob" <newsprof...@gmail.com> wrote:
>
> > > > On 1 Mar, 12:06, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > > On 1 Mar, 11:37, "elyob" <newsprof...@gmail.com> wrote:
>
> > > > > > On 1 Mar, 09:35, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > > > > On 1 Mar, 01:51, "elyob" <newsprof...@gmail.com> wrote:
>
> > > > > > > > On 1 Mar, 00:12, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
> > > > > > > > > elyob wrote:
> > > > > > > > > > Hi, rather than writing a script, I'm looking to run through a whole
> > > > > > > > > > table and update each row individually with data from each row.
>
> > > > > > > > > > This is as far as I've got, and I'm stuck.
>
> > > > > > > > > > update master_copy a
> > > > > > > > > > set pt = GeomFromText('POINT(b.lat,b.lng)')
> > > > > > > > > > where a.id in
> > > > > > > > > > (select b.id, b.lat, b.lng from master_copy b);
>
> > > > > > > > > > Thanks
>
> > > > > > > > > Use a JOIN instead of a subquery.
>
> > > > > > > > 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;
>
> > > > > > > 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.
>
> > > > > > 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 -
>
> > > > > 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!
>
> > > > 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 -
>
> > > 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
>
> > 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 -
>
> 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)')

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 ;)

On 1 Mar, 19:29, "elyob" <newsprof...@gmail.com> wrote:
> On 1 Mar, 13:59, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 1 Mar, 13:11, "elyob" <newsprof...@gmail.com> wrote:
>
> > > On 1 Mar, 12:54, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > On 1 Mar, 12:16, "elyob" <newsprof...@gmail.com> wrote:
>
> > > > > On 1 Mar, 12:06, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > > > On 1 Mar, 11:37, "elyob" <newsprof...@gmail.com> wrote:
>
> > > > > > > On 1 Mar, 09:35, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > > > > > On 1 Mar, 01:51, "elyob" <newsprof...@gmail.com> wrote:
>
> > > > > > > > > On 1 Mar, 00:12, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
> > > > > > > > > > elyob wrote:
> > > > > > > > > > > Hi, rather than writing a script, I'm looking to run through a whole
> > > > > > > > > > > table and update each row individually with data from each row.
>
> > > > > > > > > > > This is as far as I've got, and I'm stuck.
>
> > > > > > > > > > > update master_copy a
> > > > > > > > > > > set pt = GeomFromText('POINT(b.lat,b.lng)')
> > > > > > > > > > > where a.id in
> > > > > > > > > > > (select b.id, b.lat, b.lng from master_copy b);
>
> > > > > > > > > > > Thanks
>
> > > > > > > > > > Use a JOIN instead of a subquery.
>
> > > > > > > > > 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;
>
> > > > > > > > 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.
>
> > > > > > > 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 -
>
> > > > > > 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!
>
> > > > > 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 -
>
> > > > 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
>
> > > 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 -
>
> > 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)')
>
> 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 -

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.