Matrix Multiplication in MySQL

[ianson]

Dear All,

As part of a university project I am migrating a small poll application from PostgreSQL to MySQL and there is one question in particular which has stumped me for a while now.

How would one attempt to adjust the following SQL statement which uses matrix multiplication (i.e linear algebra, see http://en.wikipedia.org/wiki/Matrix_multiplication) to work with MySQL?

The original SQL statement, which works with PostgreSQL 6:

update items set newval=COALESCE((select sum(o.oldval) from items o,votes v1,votes v2 where items.id=v1.itemid AND o.id=v2.itemid AND v1.userid=v2.userid and v1.rank<v2.rank group by items.id),0);

The error MySQL returns when executed:

ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause.

I know this can be done because the same script has a similar statement which also uses matrix multiplication but in a much simpler form, which I have managed to run successfully on MySQL.

Thank you for your time.
Les

[bradgrafelman]

Perhaps this article might interest you? Here's a quote:

The workaround

Since MySQL materializes subqueries in the FROM clause (“derived tables”) as temporary tables, wrapping the subquery into another inner subquery in the FROM clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery.

[rmarescu]

wrong post / please delete