join question

[Jim Giner]

Trying to do a quick Masters analysis and having trouble with a complex
join
$q = "update leaders as m set Score1=a.score,
Score2=b.score,
Score3=c.score,
Score4=d.score,
Score5=e.score,
Score6=f.score,
Score7=g.score,
Score8=h.score,
Score9=i.score,
Score10=j.score,
Score11=k.score,
Score12=l.score
left outer join golfers a on a.golfer = m.Player1
left outer join golfers b on b.golfer = m.Player2
left outer join golfers c on c.golfer = m.Player3
left outer join golfers d on d.golfer = m.Player4
left outer join golfers e on e.golfer = m.Player5
left outer join golfers f on f.golfer = m.Player6
left outer join golfers g on g.golfer = m.Player7
left outer join golfers h on h.golfer = m.Player8
left outer join golfers i on i.golfer = m.Player9
left outer join golfers j on j.golfer = m.Player10
left outer join golfers k on k.golfer = m.Player11
left outer join golfers l on l.golfer = m.Player12";


Keeps telling me:

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
outer join golfers a on a.golfer = m.Player1 left outer join golfers b
on' at line 2'

Anyone?

[Christoph Becker]

Post by Jim Giner
Trying to do a quick Masters analysis and having trouble with a complex
join
$q = "update leaders as m set Score1=a.score,
Score2=b.score,
Score3=c.score,
Score4=d.score,
Score5=e.score,
Score6=f.score,
Score7=g.score,
Score8=h.score,
Score9=i.score,
Score10=j.score,
Score11=k.score,
Score12=l.score
left outer join golfers a on a.golfer = m.Player1
left outer join golfers b on b.golfer = m.Player2
left outer join golfers c on c.golfer = m.Player3
left outer join golfers d on d.golfer = m.Player4
left outer join golfers e on e.golfer = m.Player5
left outer join golfers f on f.golfer = m.Player6
left outer join golfers g on g.golfer = m.Player7
left outer join golfers h on h.golfer = m.Player8
left outer join golfers i on i.golfer = m.Player9
left outer join golfers j on j.golfer = m.Player10
left outer join golfers k on k.golfer = m.Player11
left outer join golfers l on l.golfer = m.Player12";
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
outer join golfers a on a.golfer = m.Player1 left outer join golfers b
on' at line 2'
Anyone?

There are no JOIN clauses allowed in UPDATE statements.[1]

On a quick glance it seems to me that your database could need some
normalization. The database mailing list[2] might be a more appropriate
place to discuss this topic.

[1] <http://dev.mysql.com/doc/refman/5.7/en/update.html>
[2] <mailto:php-***@lists.php.net>

[richard gray]

Post by Jim Giner
Anyone?
There are no JOIN clauses allowed in UPDATE statements.[1]
On a quick glance it seems to me that your database could need some
normalization. The database mailing list[2] might be a more appropriate
place to discuss this topic.
[1] <http://dev.mysql.com/doc/refman/5.7/en/update.html>

er.. you *can* use a join clause in an update statement ...

http://stackoverflow.com/questions/15209414/mysql-update-join/15209489#15209489

[Christoph Becker]

Post by richard gray

Post by Christoph Becker
There are no JOIN clauses allowed in UPDATE statements.[1]
On a quick glance it seems to me that your database could need some
normalization. The database mailing list[2] might be a more appropriate
place to discuss this topic.
[1] <http://dev.mysql.com/doc/refman/5.7/en/update.html>

er.. you *can* use a join clause in an update statement ...
http://stackoverflow.com/questions/15209414/mysql-update-join/15209489#15209489

Oh, I was not aware of that. Thanks for pointing it out.

[richard gray]

Post by Jim Giner
Trying to do a quick Masters analysis and having trouble with a
complex join
$q = "update leaders as m set Score1=a.score,
Score2=b.score,
Score3=c.score,
Score4=d.score,
Score5=e.score,
Score6=f.score,
Score7=g.score,
Score8=h.score,
Score9=i.score,
Score10=j.score,
Score11=k.score,
Score12=l.score
left outer join golfers a on a.golfer = m.Player1
left outer join golfers b on b.golfer = m.Player2
left outer join golfers c on c.golfer = m.Player3
left outer join golfers d on d.golfer = m.Player4
left outer join golfers e on e.golfer = m.Player5
left outer join golfers f on f.golfer = m.Player6
left outer join golfers g on g.golfer = m.Player7
left outer join golfers h on h.golfer = m.Player8
left outer join golfers i on i.golfer = m.Player9
left outer join golfers j on j.golfer = m.Player10
left outer join golfers k on k.golfer = m.Player11
left outer join golfers l on l.golfer = m.Player12";
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 outer join golfers a on a.golfer = m.Player1 left outer
join golfers b on' at line 2'
Anyone?

I think the join statements should be before the assignment i.e.

$q = "update leaders as m
left outer join golfers a on a.golfer = m.Player1
left outer join golfers b on b.golfer = m.Player2
left outer join golfers c on c.golfer = m.Player3
left outer join golfers d on d.golfer = m.Player4
left outer join golfers e on e.golfer = m.Player5
left outer join golfers f on f.golfer = m.Player6
left outer join golfers g on g.golfer = m.Player7
left outer join golfers h on h.golfer = m.Player8
left outer join golfers i on i.golfer = m.Player9
left outer join golfers j on j.golfer = m.Player10
left outer join golfers k on k.golfer = m.Player11
left outer join golfers l on l.golfer = m.Player12
set Score1=a.score,
Score2=b.score,
Score3=c.score,
Score4=d.score,
Score5=e.score,
Score6=f.score,
Score7=g.score,
Score8=h.score,
Score9=i.score,
Score10=j.score,
Score11=k.score,
Score12=l.score";

[Jim Giner]

Post by richard gray

Post by Jim Giner
Trying to do a quick Masters analysis and having trouble with a
complex join
$q = "update leaders as m set Score1=a.score,
Score2=b.score,
Score3=c.score,
Score4=d.score,
Score5=e.score,
Score6=f.score,
Score7=g.score,
Score8=h.score,
Score9=i.score,
Score10=j.score,
Score11=k.score,
Score12=l.score
left outer join golfers a on a.golfer = m.Player1
left outer join golfers b on b.golfer = m.Player2
left outer join golfers c on c.golfer = m.Player3
left outer join golfers d on d.golfer = m.Player4
left outer join golfers e on e.golfer = m.Player5
left outer join golfers f on f.golfer = m.Player6
left outer join golfers g on g.golfer = m.Player7
left outer join golfers h on h.golfer = m.Player8
left outer join golfers i on i.golfer = m.Player9
left outer join golfers j on j.golfer = m.Player10
left outer join golfers k on k.golfer = m.Player11
left outer join golfers l on l.golfer = m.Player12";
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 outer join golfers a on a.golfer = m.Player1 left outer
join golfers b on' at line 2'
Anyone?

I think the join statements should be before the assignment i.e.
$q = "update leaders as m
left outer join golfers a on a.golfer = m.Player1
left outer join golfers b on b.golfer = m.Player2
left outer join golfers c on c.golfer = m.Player3
left outer join golfers d on d.golfer = m.Player4
left outer join golfers e on e.golfer = m.Player5
left outer join golfers f on f.golfer = m.Player6
left outer join golfers g on g.golfer = m.Player7
left outer join golfers h on h.golfer = m.Player8
left outer join golfers i on i.golfer = m.Player9
left outer join golfers j on j.golfer = m.Player10
left outer join golfers k on k.golfer = m.Player11
left outer join golfers l on l.golfer = m.Player12
set Score1=a.score,
Score2=b.score,
Score3=c.score,
Score4=d.score,
Score5=e.score,
Score6=f.score,
Score7=g.score,
Score8=h.score,
Score9=i.score,
Score10=j.score,
Score11=k.score,
Score12=l.score";

Interesting. Didn't think of that work-around. I'll try it! Of
course, the Masters is over for this year so it'll probably be on the
back burner for 11 months!