Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    20

    Unanswered: delete query help

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your tables are very weird, this sounds far too much like a homework assignment

    please show us what you have tried yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    20
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the COUNT from the SELECT clause of the subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •