query translate

[elijah06]

I have the following ms sql query but having problems translating it over to mysql:

update t set NewAcct = NewAcct-(select count (*) from t t2 where t2.Acct = t.acct and t2.NewNo < t.NewNo) from t

your help is appreciated.

Thank you,

Dan

 

[r937]

please describe the problems -- your computer crashes? the query runs and runs and runs but never finishes? there's a syntax error?

pretty hard for us to guess...

r937.com | rudy.ca

 

[elijah06]

The above query is in microsoft sql server syntax and i need to translate it over to mysql syntax... If I attempt to run this query i get the following error message from mysql engine:

1093 you can't specify target table 'patacct' for update in FROM clause...

Hope this helps...

Thanks,

Dan

 

[elsenorjose]

http://dev.mysql.com/doc/refman/5.0/en/update.html

"Currently, you cannot update a table and select from the same table in a subquery.

 

[elijah06]

Well that is no good for me. No wonder I kept getting that error no matter what I tried...

Thank you for all the help,

Dan

 

[elijah06]

All,

I posted the above query on the official MySQL forum and received a response on how to work around updating the table being selected. The code is as follows:

UPDATE test SET NewAcct =
NewAcct - (SELECT COUNT(*) FROM (SELECT * FROM test) AS t2 WHERE t2.Acct = test.acct AND t2.NewNo < test.NewNo);

Hope this helps,

Dan