MySQL Forums
Forum List  »  Newbie

autoincrement while doing the insert
Posted by: Rich Bernstein
Date: September 26, 2022 01:08PM

Here is my CREATE that seems to work:

$sql = "CREATE TEMPORARY TABLE BC_PR_list_temp(
id INT AUTO_INCREMENT primary key NOT NULL,
taxyear VARCHAR(10),
employee_email VARCHAR(80),
employee VARCHAR(80),
qualified_dollars int NOT NULL DEFAULT 0,
title varchar(80),
role varchar(80),
num_BC int NOT NULL DEFAULT 0,
num_PR int NOT NULL DEFAULT 0,
tipe varchar(4),
item varchar(80),
dollarsPerBC int NOT NULL DEFAULT 0,
dollarsPerPR int NOT NULL DEFAULT 0,
business_component_string varchar(120),
project_string varchar(120),
CONSTRAINT ONLYONE UNIQUE(employee_email, item, qualified_dollars)
)";

$iRc=$this->db->query($sql);

And here is my insert which yields an error Duplicate entry 1735 for key BC_PR_list_temp.PRIMARY. How can I auto increment the id while doing the insert?

$sql = "INSERT IGNORE INTO BC_PR_list_temp SELECT DISTINCT srat.id, srat.taxyear , srat.employee_email , srat.employee , srat.qualified_dollars
,e.employee_title as title,e.role as role,e.num_BC ,e.num_PR,'BC','',srat.qualified_dollars/e.num_BC as dollarsPerBC,
srat.qualified_dollars/e.num_PR as dollarsPerPR, e.business_component_string,e.project_string
FROM survey_results_activities_temp srat, employees e
WHERE campaign= ? AND email= ? AND e.employee_email=srat.employee_email AND srat.employee_email IN (SELECT srat.employee_email FROM employees WHERE FIND_IN_SET(?, e.business_component_string))";

$query2 = $this->db->query($sql, array($campaign, $email, $BC));

Options: ReplyQuote


Subject
Written By
Posted
autoincrement while doing the insert
September 26, 2022 01:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.