CODEWITHSUNDEEP
mysql
Dumb_Shock
Dumb_Shock

Reputation: 1008

Unknown column in where clause -- Mysql

Well i am trying a statement like this..

UPDATE pubs SET pubs.id=parentid.id WHERE parentid.title=pubs.title

well id is the primary key of parentid ..

the error thrown up is

Error:Unknown column 'parentid.title' in 'where clause'

Help appreciated

Upvotes: 1

Views: 2638

Answers (2)

Praveen E
Praveen E

Reputation: 976

The problem in your statement is you have used parentid table directly in the where clause. First of all, all the tables you are going to use must be in the 'FROM' clause. Once a table is in 'FROM' clause, you can use it in 'WHERE'.

As you are expecting a relation between the tables in the where clause as parentid.title=pubs.title, you can use this relation for JOINING the both tables.

So, instead of WHERE, you can JOIN the both tables as 'pubs JOIN parent ON pubs.title = parentid.title'

Upvotes: 0

hayesgm
hayesgm

Reputation: 9096

I'm not sure what you're looking for. The key is that you need to specify the parent-child join in your SQL.

If your parents are already setup in a self-referential join, use the following:

UPDATE p
SET p.id=parentid.id
FROM pubs p
JOIN pubs parent
ON parent.id = p.parentid
WHERE parent.title=p.title

If you're looking to define parent-child relationships based on the titles, use this:

UPDATE p
SET p.id=parentid.id
FROM pubs p
JOIN pubs parent
ON parent.title=p.title

Upvotes: 3

Related Questions