Why am I getting the error: "Ambiguous column" in my query?-CodePudding

In this query I inserting records into a new empty table I created. These records are derived from another table where I am left joining that table to itself, in order to output records that are not included in the recent table that is appended on top of an older table. So basically it outputs records that were deleted.

CREATE DEFINER=`definer` PROCEDURE `stored_procedure_name`()
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
    START TRANSACTION;

    INSERT INTO exceptions_table (
    `insert_date`, 
    `updated`,
    `account_number`,
    `id_number`)
    SELECT
    `insert_date`, 
    `updated`,
    `account_number`,
    `id_number`
    FROM original_table ot1
    LEFT JOIN original_table ot2 
        ON ot1.`account_number` = vdcaas2.`account_number`
        AND ot2.`insert_date` = '2022-12-20'
    WHERE ot1.`insert_date` = '2022-12-10'
    AND ot2.`account_number` IS NULL;

    COMMIT;

END

I get an error stating: "SQL Error: Column "insert_date" in field list is ambiguous.

I'm not sure why because I have specified which table I am grabbing "insert_date" from when INSERTING and when SELECTING and JOINING..

CodePudding user response：

Every row in your query has two columns called insert_date: one from the table you've aliased as "ot1", and one from the table (as it happens, the same table) you've aliased as "ot2".

The database system doesn't know which one you want, so you have to tell it by writing either "ot1.insert_date" or "ot2.insert_date", just as you do elsewhere in the query:

... ot2.`insert_date` = '2022-12-20'
...
... ot1.`insert_date` = '2022-12-10'

The same is true of the other columns you've listed to select.

CodePudding user response：

You need to change this

 SELECT
  `insert_date`, 
  `updated`,
  `account_number`,
  `id_number`

to this

 SELECT
  ot1.`insert_date`, 
  ot1.`updated`,
  ot1.`account_number`,
  ot1.`id_number`

or this

 SELECT
  ot2.`insert_date`, 
  ot2.`updated`,
  ot2.`account_number`,
  ot2.`id_number`

or some combination

CodePudding user response：

Issue

SQL Error: Column "insert_date" in field list is ambiguous error means that the query is trying to reference the "insert_date" column from both tables, ot1 and ot2.

Try the following:

SELECT
    ot1.`insert_date`, 
    ot1.`updated`,
    ot1.`account_number`,
    ot1.`id_number`

Also, you have a typo in your query:

ON ot1.`account_number` = vdcaas2.`account_number` ->  ON ot1.`account_number` = ot2.`account_number`