error code: 1054: unknown column

Question

I am trying to execute the following statement:

DELETE a
FROM db.table1 a
    INNER JOIN (SELECT domainname
                FROM db.table1
                WHERE domainname LIKE '%.uci.edu'
                GROUP BY keyvalue
                HAVING COUNT(1) > 1
        ) b ON a.id = b.id

I got the error mentioned in my title. What I'm trying to achieve has been explained here: How to make the result of one query be in the NOT IN clause?

Please, help me to do this task. Why b.id is unknown ?? I have no experience in such type of Join. I understand joining two different tables, but for this statement can anybody explain please ? Please, note that the domainname is unique value. The primary key is the id and it is auto_increment but I don't use it to differentiate between columns. I mainly use another value domainname_no which is unique.

EDIT: THE command above does not work.

what I need 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.

each domain might have more than one certificate associated with key. If one or more certificate with different domain name have the same key, I want to delete it.

Now, I have two domain names which are a.yahoo.com and b.yahoo.comhave the same key. I need to delete b.yahoo.com and any domain name that have the same pattern like '%.yahoo.com' and not equal to a.yahoo.com which is the distinct value.

Currently, what I'm doing is quering the distinct values:

select distinct domainname 
from db.table1
where domainname like '%.uci.edu'
group by keyvalue
order by domainname

Then delete any value that is not in the distinct list and have the same pattern %.yahoo.com

delete from db.table1 where domain not in ( I type the list of distinct domain 
names manually)
and domainanme like '%.yahoo.com';

i hope the problem is clear now.

But this command does not work. For details see: How to make the result of one query be in the NOT IN clause?

What is the `GROUP BY keyvalue` supposed to do? Can you show sample data of the `domainname` table? — ypercubeᵀᴹ, Aug 06 '12 at 15:48
The keyvalue is a number for example. 867676. If found duplicate for two distinct domainnames, I want to delete the duplicated record. — Jury A, Aug 06 '12 at 17:08

score 0 · Accepted Answer · answered Aug 06 '12 at 15:48

you did not specify the ID for the SubQuery. Try this instead:

DELETE a
FROM db.table1 a
    INNER JOIN (SELECT id, domainname             -- added ID field
                FROM db.table1
                WHERE domainname LIKE '%.uci.edu'
                GROUP BY keyvalue
                HAVING COUNT(1) > 1
        ) b ON a.id = b.id                        -- ID was used here

score 0 · Answer 2 · answered Aug 06 '12 at 15:48

0

You're not joining two tables, your joining table1 with the result of a query, and you're not including the id column in that query. Try SELECT id, domainname at the beginning of the subquery.

answered Aug 06 '12 at 15:48

Thilo

17,565
5
68
84

error code: 1054: unknown column

2 Answers2