#1054 - Unknown column in 'where clause'

Question

I have this query in mysql database:

INSERT INTO `table1`( `text1`, `text2`, `link` )

SELECT 
    `text1`,
    `text2``,
    `link`

FROM `table2`

WHERE `table1`.`code` = `table2`.`code`;

I get an error:

#1054 - Unknown column 'table1.code' in 'where clause'

What am I doing wrong? I have no aliases, I tried HAVING instead of WHERE, I tried INNER JOIN but no success. My code columns have no indices.

Answer 1

First of all you should show also the structure of table2 to let us help you better.

Otherwise, the error is due to the lack of table1 in your query. As code is not present in your INSERT statement I may assume that it is an autoincrement field.

Your query (I suppose, as I can't see the definition of table2, as wrote before), could be

SELECT
    t2.text1,
    t2.text2,
    t2.link
FROM table2 AS t2
    INNER JOIN table1 AS t1 ON (t1.code = t2.code)
;

If the link between table1 and table2 is 1-to-many or many-to-many, just add a DISTINCT after the SELECT keyword to avoid duplicated results or change your query in

SELECT
    t2.text1,
    t2.text2,
    t2.link
FROM table2 AS t2
WHERE EXISTS (
    SELECT 'x'
    FROM table1 AS t1
    WHERE t1.code = t2.code
)