Member Avatar for cristi08

Today is a long day !
I wish to replace the entire old data from a table with new one. Update is not helping me cause i do not have an autoincrement field. What to do?

Thank you in advance

  • 2 Contributors
  • 3 Replies
  • 112 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by cristi08

Dani AI

Generated

As noted this is an N:M reporting table and suggested adding an auto‑increment. Both approaches work in some cases, but they miss a few practical concerns for a join/report table: preserving indexes/constraints, avoiding long locks, and making the swap atomic so readers never see a half-updated dataset.

A robust pattern is an atomic "build-then-rename" swap: create a new table with the same structure, populate it from the source joins, then rename the tables in one statement so the new table replaces the old instantly. Example workflow:

CREATE TABLE report_new LIKE report;
INSERT INTO report_new
SELECT ... FROM ... ;   -- build the new rows (ensure same indexes/columns)
RENAME TABLE report TO report_old, report_new TO report;
DROP TABLE report_old;

RENAME TABLE can perform the swap atomically; see the MySQL docs for details: RENAME TABLE. Make sure report_new has the same indexes and foreign keys (if needed) before renaming.

If you cannot rename (foreign keys reference the table, or you must keep exact constraints in place), use a transaction to do the replace in-place, but beware: TRUNCATE TABLE is non-transactional and may fail with FK constraints — prefer controlled DELETE + INSERT inside a transaction for InnoDB, or use a staging table as above. See TRUNCATE TABLE for behavior details.

For N:M link tables, prefer a composite primary key on the two FK columns (or a unique constraint) rather than relying on an auto-increment surrogate; that prevents duplicate pairs and is semantically clearer. Always test the chosen method on a copy of the database, verify indexes and constraints on the new table, and keep a backup before doing production swaps.

Recommended Answers

All 3 Replies

Member Avatar for cristi08

Today is a long day !
I wish to replace the entire old data from a table with new one. Update is not helping me cause i do not have an autoincrement field. What to do?

Thank you in advance

It was so simple, use delete queries and after use select and insert query.....amazing

Member Avatar for xylude

Why not just add an autoincrement field? It should still let you do that if you don't already have one.

Member Avatar for cristi08

It a N:M table, it a report with data from many table

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.