...

View Full Version : newb conditional update question



Tynan
02-27-2007, 08:01 PM
hello

I've painfully written the following query which works



SELECT MemNo, Title, FirstName, LastName, Company
FROM All_Members
WHERE Company
IN (SELECT Company
FROM All_Members
WHERE Corporate = 'y'
GROUP BY Company
HAVING count( * ) < 4
) ORDER BY Company


I now need to be able to run an UPDATE on the results returned by that query, my fingers and toes MYSQL knowledge runs out at this point

This, please don't laugh, is supposed to clear the corporate field for the results returned


UPDATE All_Members SET Corporate = ''
WHERE Company
IN (SELECT Company
FROM All_Members
WHERE Corporate = 'y'
GROUP BY Company
HAVING count( * ) < 4
)
and gives me the following error



Error

SQL query: Documentation

UPDATE All_Members SET Corporate = '' WHERE Company IN (
SELECT Company
FROM All_Members
WHERE Corporate = 'y'
GROUP BY Company
HAVING count( * ) <4
)

MySQL said: Documentation
#1093 - You can't specify target table 'All_Members' for update in FROM clause


I'd be stunned if there's only one problem in there

I'm on MYSQL version: 4.1.21

Thanks for your time and apologies for any vague or dodgy anythings

Tynan
02-27-2007, 09:05 PM
after some weary googling on updates using subqueries, I got that UPDATE query to work by using a backup of the table to do the query against

that seems to have worked, so good

I assume that there's a way to run it on a table all in one go though?

And it's a 133 second query on a 1100 record table, that's a slow and inefficient query I take it?

guelphdad
02-28-2007, 12:42 AM
You can't update a table you are selecting records from.

Also note that your GROUP BY clause is incorrect. When you use a GROUP BY clause it needs to contain all the columns that are in your SELECT statement.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum