CODEWITHSUNDEEP
phpmysqlinsert-update
Smith
Smith

Reputation: 5961

key violation when inserting mysql

I have this SQL code which inserts into a database named daily. I regularly update the daily database like this:

INSERT INTO daily 
(SELECT * FROM dailytemp 
WHERE trim(`Point`) <>'' 
AND trim(`Point`) IN (Select trim(`Point`) FROM `data`));

I believe I am getting the error because both tables have id primary columns.

I want the data inserted, but a new id generated for any duplicate found.

I tried to list the columns names, but it's about 20 columns, which might make the process cumbersome.

What is my best option?

Edit

The table defination of daily and daily_temp are the same, all the folumns are varchar(100) with and id of (bigint)

id bigint
col1 varchar(100)
col2 varchar(100)
col3 varchar(100)
etc..

Upvotes: 0

Views: 431

Answers (3)

Smith
Smith

Reputation: 5961

i decided to use a stored proceedure to perform the action, listing all colums name instead of this headache

thanks to all those who helped. Another reason i did that was that i needed to insert a date for the daily table for each set of data inserted, so i created a date column for the dail table only

INSERT INTO daily (col1, col2, etc..., created) 
SELECT col1, col2, etc..., 'mydate' from daily_temp  WHERE 
trim(`Point`) <>'' AND trim(`Point`) IN 
(Select trim(`Point`) FROM `data`));

thanks all

Upvotes: 0

peterm
peterm

Reputation: 92805

A possible solution might be to use a BEFORE INSERT trigger and a separate table for sequencing (if you don't mind).

Sequence table 

CREATE TABLE daily_seq(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY);

Now the trigger 

DELIMITER $$
CREATE TRIGGER tg_daily_insert
BEFORE INSERT ON daily
FOR EACH ROW
BEGIN
  INSERT INTO daily_seq VALUES(NULL);
  SET NEW.id = LAST_INSERT_ID();
END$$
DELIMITER ;

Now you can insert whatever you want to daily, id value will replaced with unique auto incremented one.

Here is SQLFiddle demo

Upvotes: 1

RandomSeed
RandomSeed

Reputation: 29769

Indeed, you are certainly trying to insert a row with an id that already exists in daily.

If this does not pose any referential integrity issue, you could regenerate the id's in dailytemp so that they do not overlap with any one in daily. This might do the trick:

SET @offset =  (SELECT MAX(id) FROM daily) - (SELECT MIN(id) FROM dalytemp) +1;
UPDATE dailytemp SET id = id + @offset;

Then try your INSERT.

If rows can be inserted into daily by another process than the one you described, or if for any reason a id is skipped in this table (eg. if the column is AUTO_INCREMENT), then the problem will reoccur. You may want to include this preliminary UPDATE in your daily procedure.

Upvotes: 1

Related Questions