[ code ] and [ / code ] (without spaces) gives you formatting:
SELECT *
FROM
( SELECT transaction_id, id_transaction_id, original_transaction_id
FROM transactions
WHERE (mdf_14 IS NULL
OR length(mdf_14) < 1
)
AND original_transaction_id IN (
SELECT transactions.transaction_id
FROM transactions
LEFT JOIN actions ON transactions.transaction_id = actions.transaction_id
WHERE mdf_14 IS NOT NULL
AND action_type = 'sale'
AND success = 1)
) A
LEFT JOIN
( SELECT transactions.transaction_id AS transaction_id, mdf_14
FROM transactions
LEFT JOIN actions ON transactions.transaction_id = actions.transaction_id
WHERE mdf_14 IS NOT NULL
AND action_type = 'sale'
AND success = 1
) B ON A.original_transaction_id = B.transaction_id;
Two performance problems. (If your tables are small, you can ignore this diversion):
* IN ( SELECT ... ) is poorly optimized (until some very recent version); turn it into a JOIN if possible.
* FROM (SELECT...) JOIN (SELECT...) -- This builds two temp tables without indexes (until very recently), then has to work very hard to match up the rows.
For either problem, you could CREATE TEMPORARY TABLE with the subquery:
CREATE TEMPORARY TABLE tmp1 ( INDEX (...) ) SELECT ...;
Note the addition of an INDEX as you do the inserts.
> Can one do this in ANSI SQL alone
Well, all of the above is at least in MySQL's SQL; if there are any MySQLisms, there may be a workaround to get ANSI.
Back to the UPDATE statement... See the "Multiple-table syntax" in
http://dev.mysql.com/doc/refman/5.6/en/update.html
But it does not make sense to modify A.mdf_14, since A is not a real table. Did you mean `transactions`?
Something like this is valid (I think), but probably does not do what you are asking for:
UPDATE transactions A
JOIN transactions B ON A.original_transaction_id = B.transaction_id;
SET A.mdf_14 = B.mdf_14
WHERE ( A.mdf_14 is null
OR A.mdf_14 = '' )
AND LENGTH(B.mdf_14) > 1