delete query help

khdani · #1 (**permalink**) 12-25-08, 09:03

Hello,
I've two tables
1) Governor_Of(governor_name, state_name)
2) Mayor_Of(mayor_name,city_name)

I want to delete a row of governor where a state is governed by more than one governor including this which i want to delete and that governor is not a mayor.
For example:
Governor_Of
_________
Michael NY
George NY

Mayor_Of
________
Michael NYCity

I want to delete George from Governor_Of
Please help....

Thank You

r937 · #2 (**permalink**) 12-25-08, 09:04

your tables are very weird, this sounds far too much like a homework assignment

please show us what you have tried yourself

khdani · #3 (**permalink**) 12-25-08, 09:12

i've tried, this

Code:

DELETE FROM Governor_Of
WHERE governor_name NOT IN (SELECT mayor_name FROM Mayor)
AND governor_name IN (SELECT governor_name, COUNT(state_name)
		FROM Governor_Of
		GROUP BY governor_name
		HAVING COUNT(state_name) > 1
	)
;

but it says too many values in line 3 at SELECT

r937 · #4 (**permalink**) 12-25-08, 10:03

remove the COUNT from the SELECT clause of the subquery

Internet Services

Web Development

Digital Marketing

Consumer Tech