MySQL – Fixing ‘Unknown Column’ Query Error

MySQL

I get error (1054): Unknown column 'wp_jpnx_survey_code.supplier_id' in 'field list'
But that column does exist… Does anyone know why or how to fix this?

This is the code I used:

CREATE VIEW survey_codes_complete_overview AS SELECT *,
wp_jpnx_survey_code.supplier_id AS a_id,
wp_jpnx_survey_supplier_info.supplier_id AS b_id FROM
wp_jpnx_survey_code AS a LEFT JOIN wp_jpnx_survey_supplier_info AS b
ON a.supplier_id = b.supplier_id;

Best Answer

You are using aliases for those tables. So, specify the alias when selecting the column.

CREATE VIEW survey_codes_complete_overview AS 
SELECT *, 
a.supplier_id AS a_id, 
b.supplier_id AS b_id 
FROM wp_jpnx_survey_code AS a 
LEFT JOIN wp_jpnx_survey_supplier_info AS b 
ON a.supplier_id = b.supplier_id

Related Solutions

MySQL Trigger On UPDATE “Unknown Column” Error

Get rid of UNIX_TIMESTAMP() everywhere. DATETIME fields can be compared directly.

Once you have done that, you will probably notice the missing @ that is causing the error.

I think it can be simplified down to only:

CREATE TRIGGER ...
IF COALESCE(OLD.updateDate, OLD.insertDate) < CURRENT_DATE() - interval 3 month
    INSERT INTO three_month_ago_dad_records SELECT OLD.*;
END IF;
END;

Mysql – How to we create a table from stored procedure

I would do it with 2 steps: I would create a table template allowing to create as many tables as you want based on that table. Advantage is you only need to modify the table template, you don't need to touch the SP.

Create a table template;

CREATE table tableName_template(
    ID INT NOT NULL,
    SNAME VARCHAR(100)  NOT NULL,
    SSTATE VARCHAR(100) NOT NULL,
    TIME_STAMP VARCHAR(45) NOT NULL,
    constraint FK_SENSOR
        foreign key (SNAME)
        references sensorInfo(SName)
        on delete set null
);

Use this code

DELIMITER $$
CREATE DEFINER=`xxx`@`localhost`  (do not use root !! Create dedicated account) 
PROCEDURE `create_tb_one` (In tableName varchar(255))
BEGIN

    If not exists (select 1 FROM information_schema.tables
    WHERE table_schema=DATABASE()
    AND table_name=tableName ) 
    Then 

        SET @sql = CONCAT('CREATE TABLE ',tableName,' LIKE  tableName_template');
        PREPARE s FROM @sql;
        EXECUTE s;
        DEALLOCATE PREPARE s;
    END IF;


END $$
DELIMITER ;

Best Answer

Related Solutions

MySQL Trigger On UPDATE “Unknown Column” Error

Mysql – How to we create a table from stored procedure

Related Question