On Jan 18, 5:03*pm, Dvd Avins <dvdav... (AT) pobox (DOT) com> wrote:
I urge you to use explicit JOINs rather than implicit comma joins.
You'll save yourself a lot of grief in the future and your intentions
will be easier to understand as it separates JOIN criteria from
SELECTion criteria.

Now the subquery is certainly the proper supported way to achieve what
you want. As Erick points out, when the calculation is complicated,
there are sound maintenance reasons for coding it only once.

There is another way that this can be achieved. It is specifically
advised against in the manual, but I have always found it to work.

Your query would be re-written as:
INSERT INTO c SELECT
a.f AS f,
a.x * (@tmp:=(a.r / b.r)) AS x,
a.y * @tmp AS y,
a.z * @tmp AS z
FROM a
JOIN b USING(f)
WHERE a.g != "p" AND b.g = "p"

Notes on the above query:
1) All the brackets used in the assignment of eh variable @tmp are not
strictly necessary. I use them to keep my intentions clear.
2) I have taken the liberty of changing the implicit join to an
explicit one. This is not actually required for the query to work.
3) Whilst I have always found this to work, you should read the
paragraph beginning: "As a general rule," on the page
http://dev.mysql.com/doc/refman/5.6/...variables.html, which I
alluded to above.

Captain Paralytic <paul_lautman (AT) yahoo (DOT) com> wrote:

I don't know what your business is, but relying on undefined
behavior can sometimes result in the termination of your contract.
Or your life

If we assume for a moment that there are /good/ reasons to
single out the calculation of (a.r/b.r) then we can do it
with a "derived table" subquery like so:

INSERT INTO c (f, x, y, z)
SELECT f, x*tmp, y*tmp, z*tmp
FROM (
SELECT a.f, a.x, a.y, a.z, a.r/b.r AS tmp
FROM a JOIN b USING(f)
WHERE a.g != "p" AND b.g = "p"
) AS t1

This has the advantage of being SQL compliant and does not rely
on undefined behavior. However I severely doubt that the runtime
behavior is any better than that of the original query. Also it's
not exactly easier to read :/

Note: instead of aliasing the selected columns for mapping to
columns in c, I rather used an explicite column list for the
INSERT statement. This is a matter of taste.


XL

On Jan 19, 12:42*pm, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:

I said: "Now the subquery is certainly the proper supported way to
achieve what you want.", which is a more concise way of saying "This
has the advantage of being SQL compliant and does not..."

I also carefully included: "It is specifically advised against in the
manual" and went on to include the precise reference in the manual
itself.

So I think your post is rather superfluous.