0

I have 2 tables (with identical structure) and I want both to update after a form submission and both have the exact same data.

The tables structure are as below:

id | parent_id | name | surname

The id is the primary key, which means that I cannot execute the following query because it throws an error (Duplicate entry '1' for key 'PRIMARY'):

INSERT INTO table_2 SELECT * FROM table_1

My goal is to keep the data identical (including the PRIMARY) in both tables after a row is updated or a new row is added. How can I do this and avoid the Duplicate entry error?

otinanai
  • 3,987
  • 3
  • 25
  • 43
  • 3
    Why do you need two tables with the same data? – Sven van Zoelen Apr 14 '15 at 14:28
  • First just asking why 2 tables need exactly the same data. Seems odd. But, I would keep the primary key unique to both. But then have a column called `row_id` or something for each table. That way if needed, you could grab a unique row, or by using your own hook `row_id` you could grab both records. – Damon Apr 14 '15 at 14:29
  • Why dont you trigger 2 queries? And as @SvenvanZoelen said, why2 tables? – B001ᛦ Apr 14 '15 at 14:29
  • two identical tables is bad DBA juju... – bpeterson76 Apr 14 '15 at 14:29
  • 2
    hey guys. just leave op be with his tables, geez... – Félix Adriyel Gagnon-Grenier Apr 14 '15 at 14:30
  • I know it's odd but I work with a CMS and bilingual support is limited. So, I have 2 forms (one for each language) that point to a different table. However, when I need to show submitted data, I can only get data from 1 table (based on language selected). – otinanai Apr 14 '15 at 14:30
  • Are both forms on the same page or 2 different forms on 2 different page(s)? – Damon Apr 14 '15 at 14:34
  • @dragonslovetacos There are 2 different forms on 2 different pages. What I need though is the sql query to make each table identical. – otinanai Apr 14 '15 at 14:36
  • 2
    *"How can I do this and avoid the Duplicate entry error?"* - Refer to this Q&A http://stackoverflow.com/q/812437/ – Funk Forty Niner Apr 14 '15 at 14:39

1 Answers1

1

Because your forms are on two different pages, you need some way to connect the two when creating the second record.

Using language as an example, I would have my "primary" language form on one page. Submit that information to your "default/primary" language table.

Then, on your "secondary" table, I would include a select input that listed out my "primary" languages. The values would be the primary key of your "primary" language. The user has to choose a parent language to connect to. Otherwise the secondary language has no idea who it belongs to.

On your secondary table, I would not have an auto_incrementing primary key. That way you can share the same ID as your parent table.

I would create a foreign key to the "secondary" table that was related to the "primary" table id field.

This will allow you to run queries like:

Not tested at all - and probably incorrect syntax.

SELECT * FROM `primary` WHERE something JOIN `secondary` ON `secondary.id` WHERE `secondary.id` = `primary_id`

But, that would give you an idea of what I'm talking about.

Edit

Based on our conversation, it sounds like you will either need to drop your secondary table on each insert (to clear the primary keys). Or I found this thread on resetting the key that might be helpful.

Community
  • 1
  • 1
Damon
  • 4,151
  • 13
  • 52
  • 108
  • this is a nice approach but my problem is that I cannot change the table structure that's why I needed a query similar to `INSERT INTO table_2 SELECT * FROM table_1` that will copy everything from one table to another and ignoring sql errors. When I say "ignoring" I mean having a way to replace or update a row. – otinanai Apr 14 '15 at 14:55
  • 1
    I would either check out the link that @Fred -ii- provided, or it sounds like when you insert table 1, in the same query, select all of table 2, delete everything - then copy all of db 1 to db 2. – Damon Apr 14 '15 at 15:01
  • This was my next thought: delete table1 and make a new copy of table2. However, I'm not sure if by deleting all entries (instead of the table) and copying the new values it affects the autoincrement or in general the properties of the primary key. – otinanai Apr 14 '15 at 15:04
  • You would have to drop it or reset the primary key. [I found this thread](http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key) that might be of some help. – Damon Apr 14 '15 at 15:07
  • Please make your 2nd suggestion as an (edit) answer so that I accept it. Thank you for your help! – otinanai Apr 14 '15 at 15:11
  • Awesome - glad I was of (some) help! – Damon Apr 14 '15 at 16:31