0

I want to delete values that are not from a specific set of values. I don't know these values in advance. I can know these values by querying them & I want to do the process in one step. I did the following statement:

delete from db.table1 where domain not in 
(
select distinct domainname 
from db.table1
where domainname like '%.uci.edu'
group by keyvalue
order by domainname
) 
and domainanme like '%.uci.edu';

Knowing that the domainname is a unique field, and the primary key for table1 is auto_increment number.

What I need to do is the following: I have a list of domain names. say: aa.yahoo.com, bb.yahoo.com, cc.yahoo.com, aa.msn.com. Each domain name has a key value. If the key is shared, I want to delete this record. So, I selected the distinct values grouped them by the key value. If the record in not in the distinct set, and have the same top level domain, then I don't need it.

I might be wrong in my query. SQL gives me error: 1093: you can' specify target table table1 for update in FROM clause.

Please, help in achieving this purpose by any efficient way. I have a very long list of sheared top level domain names, I only need the ones with distinct key value. If there is a universal solution that will do this without the need of going on every domain name one by one (like my example) that is better.

EDIT: domainname is NOT unique field. I use another field: domainnameNo as unique field

Jury A
  • 19,192
  • 24
  • 69
  • 93

1 Answers1

0

I think this is what you are looking for:

DELETE a
FROM db.table1 a
    INNER JOIN (SELECT id
                FROM db.table1
                WHERE domainname LIKE '%.uci.edu'
                GROUP BY keyvalue
                HAVING COUNT(1) > 1
        ) b
        ON a.id = b.id;
Omesh
  • 27,801
  • 6
  • 42
  • 51
  • It gives me a syntax error near `a` (underline `a` with red). Are you sure from the syntax? Shall I write `as` before `a` ?? The error code: 1064. – Jury A Aug 06 '12 at 13:46
  • @ Omesh: Also, what is the meaning of `COUNT(1)` ?? I know `COUNT(*)` ?? Can you please give brief words explaining the statement or link that I refer to ? The statement does not work with me. There is a problem but I'm not able to figure out what is it ? The error code stating it is a syntax error. Any idea ? – Jury A Aug 06 '12 at 14:19
  • @ Omesh: Can you explain what is having count (1) > 1 refer to ?? what is the value 1 inside count function ? Can you please explain your statement ?? – Jury A Aug 06 '12 at 17:10
  • sorry for the late reply. COUNt(1) and COUNT(*) are same but COUNT(1) and COUNT(column_name) are different. see http://www.rustyrazorblade.com/2007/01/count1-vs-count-any-difference/ & http://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct – Omesh Aug 07 '12 at 04:44
  • Edited my post try it now. It should be `DELETE a` when deleting records using join. It should work now. – Omesh Aug 07 '12 at 04:47