guradio guradio - 7 months ago 20
SQL Question

insert into using same row(same primary key) mysql php pdo

INSERT INTO courses1 (sys_id,name, location)
SELECT *
FROM courses
WHERE sys_id= $sysid


I have the two tables above and I want to insert in course1 what I have in course. But I get problem with
sys_id
because I get
Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'' in
so what I did is a added a column in courses1 named
system_id
but then I get another error saying column not match.

Tried


INSERT IGNORE INTO
but ther result is not what I am expecting. The result I want is to have the data from course to course1 everytime I make a transaction. I want the data not to be overriden.


  1. What is the best solution for
    insert into
    when inserting same row(same primary key)?



UPDATE


My aim is to back-up the data (historical data). I want to record the data before editing it. basically what i am doing is inserting the data in another table before updating it

Answer

This question is a perfect example that demonstrates the fact that to give an answer, one have to understand the context of the question, not just write an automated post triggered by some keyword in the question.

If you want a history table, it have to have a different structure from the main table. At least it should have a non-unique key for the id from the main table.

So, make a history table of the same structure, but add a main_id field. That gives you a structure like

id
manin_id
name
location

Then, to add a record into a history table,

INSERT INTO courses_history SELECT NULL, * FROM courses  WHERE sys_id=?

you may want also to add an index (non-unique) for the main_id field.

This way it will allow to store multiple "replicas" of the same row from the main table.

To get the last replica, you need a query like this:

SELECT * FROM courses_history WHERE sys_id=? ORDER BY id desc
Comments