How to RollBack the data?

Hi,

I am have database with Category_Product table

Category Product

cat1 prod1 cat1 prod2 cat1 prod3 cat1 prod4 cat2 prod1 cat2 prod2 cat2 prod3 cat2 prod4 cat3 prod1 cat3 prod2

i have run the query

UPDATE [sample test].[dbo].[Category_Product ]

SET [Product] = 'prod1'

WHERE [Product]='prod4'

COMMIT

Now the Category_Product is changed. But i need to Rollback the Updated statement execution. So that only changed "prod1" can be replace with orginal value "prod4"

Can any one help me to get back the original data by writing query

Thank you

With the limited information you gave us, I assume that this may help you:

UPDATE SomeTable
SET Product = 'Prod4'
FROM SomeTable S
INNER JOIN
(
SELECT MIN(YourPrimaryKeyWhichYouDidNotmention) PK
FROM SomeTable
Where Producr = 'Prod1'
GROUP BY Category,Product
HAVING COUNT(*) > 2
) SubQUery
ON S.YourPrimaryKeyWhichYouDidNotmention = SubQUery.PK

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi,

Thanks for ur reply.

My table has no primary key.

It has 3 fields Category,Product and Quantity.

The query which u sent me updates 2 records for each category with Prod1 (one is the original with Prod1 and the other which has been changed from prod4 to prod1).But i want to modify only the records which are changed before from prod4 to prod1.

please help me out as soon as possible.

|||

Having the need to identitfy the rows which has to be updated, you should create a PK on the table (even if only temporary using an Identity) and run the query mentioned. It will pick only one row (using the MIN aggregate) and will update this accordingly to your described situation.

Jens K. Suessmeyer

http://www.sqlserver2005.de