CODEWITHSUNDEEP
sqlsql-serverselectinsert
user3681549
user3681549

Reputation:

SQL Insert existing/duplicate row into table but change only one column value?

I have Audit table with more than 50 columns and need to insert desired row (duplicate) by changing just one column value (column CreatedDate, set value to GETDATE()). I know this can be achieved by INSERT INTO SELECT * FROM but as there is more han 50 columns, code would seem messy:

INSERT INTO Audit_Table (Col1, Col2, .....CreatedDate, ......, Col50, ...ColN)
SELECT (Col1, Col2, .....GETDATE(), ......, Col50, ...ColN) FROM Audit_Table 
WHERE  Audit_Table.Id = Desired_Id

If i shouldn't change CreatedDate column value, it would be very simple:

INSERT INTO Audit_Table SELECT * FROM Audit_Table WHERE Audit_Table.ID = Desired_Id

Is there any other way to duplicate row and change only one/desired column value?

Upvotes: 3

Views: 10650

Answers (4)

santosh
santosh

Reputation: 1

try below as reference

you can use below statement to copy the all rows,
mysql> insert into newstudent select * from students;


you can use below statement to copy the specific row from table,
mysql> insert into newstudent
    -> select id, name, age, address
    -> from students
    -> where
    -> id = 1248;


you can use below statement to copy the either of the row from table,   
    mysql> insert into newstudent
    -> select id, name, age, address
    -> from students
    -> where
    -> id = 1248 or id=1249;

use limit clause also along with this

Upvotes: 0

marcothesane
marcothesane

Reputation: 6749

No. There is no way to say * except column_foo in SQL.

The workaround would be to generate the 

SELECT
  col1
, col2
, [...]
, coln
FROM foo;

statement (or parts of it) by querying the database's system catalogue for the column names in their order. There is always a table with all tables and a table with all columns. Then, make sure you put the necessary commas in the right place (or remove them where you don't need them, or generate the comma in all rows of the report but the first - by using the ROW_NUMBER() OLAP function and evaluating whether it returns 1 or something else). Finally, edit the right date column, by replacing it with CURRENT_DATE or whatever your database uses for the current day.

Good luck - Marco

Upvotes: 1

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5143

You can build upon your existing idea. Just duplicate the row (I assume, you have an auto-incrementing primary key column) and then in a separate query update the time i.e.

Do this :

INSERT INTO Audit_Table SELECT * FROM Audit_Table WHERE Audit_Table.ID = Desired_Id

And then :

UPDATE Audit_Table SET CreatedDate = GETDATE() WHERE primaryKeyID = newPrimaryKeyID

Hope this helps!!!

Upvotes: 0

Marko
Marko

Reputation: 1000

You can insert the record into a temporary table, update the CreatedDate column to GETDATE(), then insert it into the Audit_Table.

Upvotes: 4

Related Questions