-1

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?

Shevliaskovic
  • 1,562
  • 4
  • 26
  • 43
Hardik Parmar
  • 1,053
  • 3
  • 15
  • 39
  • I think he wants to add it as a new row and then update the old rows latest flag to 0, possibly to use it as some sort of audit trail? – Matt Dec 01 '14 at 14:48
  • Why do you want to do this to your data? If you want audit information you should move it to an audit and not clutter up your primary table. – Sean Lange Dec 01 '14 at 14:56
  • This is not weird way of doing edit the data Coz Suppose If suppose to one purchase is updated 5 times and I want all the edit for the comparision off all the updates & also want to distinguish the latest update is there any way other then this – Hardik Parmar Dec 01 '14 at 14:59
  • @Sean Lange you cannot mark down any quesiton coz it is weird way of doing – Hardik Parmar Dec 01 '14 at 15:01
  • I didn't mark down your question but glad you jumped to that conclusion. – Sean Lange Dec 01 '14 at 15:02
  • It is not a weird way of doing this, I would say it is a very bad way of doing this. Use an audit/history table. If you want to view the history you join the main table with the secondary table. What you are trying to do here is not properly normalized because you are trying to main the "current" row among a bunch of other rows. – Sean Lange Dec 01 '14 at 15:04

2 Answers2

3

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' 
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

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

Shantanu
  • 186
  • 1
  • 8
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – zisoft Dec 01 '14 at 15:23
  • I disagree @zisoft. It's not very detailed, but it very much is an answer to the question. – RubberDuck Dec 01 '14 at 15:43