Using a SELECT inside an UPDATE query

Databases
1

I’m guessing there is not a good way of doing an UPDATE to the memberNumber column while also using the following SELECT query. Am I right?

select memberNumber, count(*) as total from users where memberNumber > 0 group by memberNumber having total > 1 order by total desc;

2

In MySQL joins can be used in update statements and you can also use on duplicate key update to achieve a similar behavior with an insert statement. However, I don’t really understand what you’re trying to do to offer any specific SQL.

3

The issue is not that you use the field memberNumber in both UPDATE and SELECT. The issue is the result that SELECT is providing. It has to give a one record result.

Not sure that query is OK. Most of the databases I know will not accept the alias total in the condition.

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.