Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: remove certain rows from table

    Hi everyone - how do I remove certain rows from a table based on the following criteria?

    i have a table1 which holds fields "person" and "type". The current table has the following problem:

    person type
    1 a
    2 b
    3 a
    3 b

    I actually want person 3's type to default to a. The table should be a distinct list of persons. So the criteria would be:

    "for persons with two rows in the table, remove the row which has type = b"

    how do i do this?

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    delete from mytable as x where type = 'b' and exists (select 1 from mytable as z where z.person = x.person and type <> 'b')

    Andy

  3. #3
    Join Date
    Jul 2011
    Posts
    33
    Hi andy, thanks very much for the reply - having looked at your answer i understand what is going on. one thing i get though is

    SQL0100W - No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

    and I have doubled checked that the table I am selecting on exists, and that the problem I have described does indeed occur within the table. Any thoughts on this?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the result of:

    Code:
    select * from mytable as x where type = 'b' and exists (select 1 from mytable as z where z.person = x.person and z.type <> 'b')

    Andy

  5. #5
    Join Date
    Jul 2011
    Posts
    33
    it yields an empty table. However select * from mytable returns data, of which the problem of having two rows for one person in some persons exists. very strange.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Something must be getting lost in translation. Can you dump the actual rows that show that more than one exists?

    Andy

  7. #7
    Join Date
    Jul 2011
    Posts
    33
    ep_type is case sensitive.....which means I am an idiot! problem solved. Thanks for all your help Andy, your original code works

Posting Permissions

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