Copy data from one existing row to another

Question

I have one table for storing status and comment.Some more columns are there.Now each time one new comment is posted I need to insert one new row in that table(change is only in the comment field , status and all other column values will remain as it is).What's the best way of doing this?

Answer 1

Assuming you're using some SQL dialect you may be able to do something similar to:

INSERT INTO table (comment, otherCol1, otherCol2, otherCol3)
SELECT 'Some comment', otherCol1, otherCol2, otherCol3 FROM table
WHERE id=existingRowId;

This will insert a new row with the comment 'Some comment' and the values for (otherCol1, otherCol2, otherCol3) copied from an existing row. That existing row is not deleted.

Is it what you are asking for?

Answer 2

The best way is to split this table into 2 tables. One table to store all values that are common for all records (with an ID value) and another table to store Comment, Status and the ID value from the first table. Its normalized design for your requirement.