Copy single row from the table in SQL

Question

I have to copy single row from the table on certain conditions.

Suppose this is my table:

ProductName ProductCount Isactive latest 
AA              1          1       1 
Bb              5          1       1

Now, suppose I want to edit the first row; what I will do is that I will not edit the first row itself, but I want to copy that particular row and then I want to update that row.

In the above scenario, I want to update ProductCount then I want the output to be as:

ProductName ProductCount Isactive latest 
AA              1          1       0 
Bb              5          1       1
AA              6          1       1 

How can we do this?

Answer 1

first do a insert into and then do an update .. it has to be in two steps ..

Answer 2

Use can do this with a Instead of Update Trigger but, this is little weird that you want to keep historical data in your main table.

This is just an idea you may have to change a bit to get it work for all your scenarios

CREATE TABLE tests
  (
     ProductName  VARCHAR(50),
     ProductCount INT,
     Isactive     INT,
     latest       INT
  )

INSERT tests
VALUES ('AA',1,1,1 ),
       ('Bb',5,1,1)

go

CREATE TRIGGER [DBO].[TRG_tests_UPD]
ON tests
INSTEAD OF UPDATE
AS
  BEGIN
      INSERT INTO tests
                  (ProductName,ProductCount,Isactive,latest)
      SELECT ProductName,ProductCount,Isactive,1
      FROM   inserted

      UPDATE A
      SET    latest = 0
      FROM   tests a
             JOIN deleted D
               ON a.ProductName = D.ProductName
  END 

UPDATE tests
SET    ProductName = 'Prdp'
WHERE  ProductName = 'AA'