var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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:
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?
delete from mytable as x where type = 'b' and exists (select 1 from mytable as z where z.person = x.person and type <> 'b')
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?
What is the result of:
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')
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.
Something must be getting lost in translation. Can you dump the actual rows that show that more than one exists?
ep_type is case sensitive.....which means I am an idiot! problem solved. Thanks for all your help Andy, your original code works