Use of Update statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use of Update statement

Author

Topic

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 01:44:41

Hello everyone,

Could anyone tell me how we can view the updated records of a table (only the records that have been updated).

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-12 : 02:06:16

you must select the rows based on any of the column that holds change of time.

send DDL and your update sentence.

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 02:47:53

hi slimt_slimt,

could you please provide me with the query to accomplish this.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-12 : 02:50:19

For that you need to have one column in the table which will tell you
these rows are only inserted and these rows are updated too.
for that either you can have column like Action with the value I or U; I for Inserted and U for Updated.
or you can have date where you can put the null value if inserted and put the date of updation if updated.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 03:14:22

Hi vaibhavktiwari83,

Could you please elaborate it with the help of a query.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 03:24:29

If you don't have a table with column(s) that hold information about a row is updated or not then it is not easy for us to give a query.
Are we talking about table design or a query on a table that already exists?

You should be more clear.

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-12 : 03:31:07

Yes webfred is correct.
You are asking query for the solution
but solution can be provide if you will have expected table structure.
but still following code will give you idea what we are talking about -

Execute the below code on your test database -


IF EXISTS ( SELECT NULL FROM SYS.TABLES WHERE OBJECT_ID = OBJECT_ID('TestTable') )
BEGIN
	DROP TABLE TestTable
END

CREATE TABLE TestTable ( TemplateID INT, TemplateName VARCHAR(50), ScheduledTime VARCHAR(5), TillTime VARCHAR(5), DBAction CHAR(1), ActionTime DATETIME)

INSERT INTO TestTable
SELECT '1','Template1','7:00','11:00','I','08/12/10 12:52 PM'
UNION ALL
SELECT '2','Template2','7:00','11:00','I','08/12/10 12:52 PM'
UNION ALL
SELECT '3','Template3','7:00','11:00','I','08/12/10 12:52 PM'
UNION ALL
SELECT '4','Template4','7:00','11:00','I','08/12/10 12:52 PM'

SELECT * FROM TestTable WHERE DBAction = 'U'
-- It will now display any records as nothing has been updated yet

UPDATE TestTable SET TillTime = '11:30' , DBAction = 'U' , ActionTime = GETDATE() WHERE TemplateID IN (2,4)

SELECT * FROM TestTable WHERE DBAction = 'U'
-- this time you will get only updated records

That means you must have a column in table like DBAction in above example to know which rows are updated.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-12 : 03:47:37

i suggest you use timestamp field or varbinary(8) field. and make a constraint that each time you update any of the fields in the table timestamp field gets updated or any varbinary field.

try this:
[/code]
--create table
create table test
(id int identity(1,1)
,nmb int
,timestamp)

--populate the table
insert into test(nmb)
select 3
union all select 4
union all select 7
union all select 5
union all select 4

---create backup
select *
into test_backup
from test

--update value in one of the rows
update test
set nmb = 10
where id = 2

--find updated fields
select
* from test
where not exists (Select * from test_backup where test.timestamp = test_backup.timestamp)

--drop all the tables
drop table test
drop table test_backup
[/code]

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 03:56:16

There are a number of methods:

Store CreateDate and UpdateDate in record. Maintain the UpdateDate using a trigger so it never fails to change. If the two are the same then the record has not been updated. However, there may be times where you import data from another database / source where you do NOT want the UpdateDate to change - having it set in the trigger is an annoyance in that situation

Store an EditNo and increment it in an Update Trigger. We use this approach (for optimistic record locking). EditNo > 1 === Modified record. For Optimistic Locking using ROWVERSION datatype may be a better approach. A column with ROWVERSION datatype is automatically changed on UPDATE (by SQL Server) ... but you then have to store the original somewhere in order to know that it has chagned, natch!)

Store changed rows in an AuditTable (again, using Update Trigger). If a record has a row in the audit table then it has changed. You could implment this by adding an Audit table, and trigger, but without needing to change the columns in the existing table.

For detecting fraud, or somesuch, restore an older backup to a new, temporary, database and compare rows in the Live database with those in the Old database. This does not prove that the record was NOT updated, only that it was not CHANGED (i.e. it will NOT show records that were Changed and changed BACK again - but a ROWVERSION datatype column WOULD have changed & WOULD be different)

On SQL 2008 there are setting for logging changes which you might be able to use (going forwards, but not retrospective AFAIK)

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 04:00:54

quote:
Originally posted by slimt_slimt

i suggest you use timestamp field

Better to use ROWVERSION nowadays I think - same thing, but WAY better name! as it avoids confuse with Date + Time which "timestamp" has nothing to do with and hence the potential for confusion.

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 04:11:44

Ha! I would never have believed that




---create backup
select *
into test_backup 
from test

would work - and preserve the timestamp.

Learn something new very day!

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-12 : 04:18:21

kristen,

true :) it does preserve timestamp. I was surprised too the first time :)

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 05:18:19

Although it will then be a bit of an issue downstream ... can't copy more rows to the table.

An alternative would be to create the table without the IDENTITY and TIMESTAMP datatypes so that more data can be appended to it easily:


select CONVERT(int, id) AS [id], nmb, convert(varbinary(8), timestamp) AS [MyTimestamp]
into test_backup 
from test
WHERE 1=0 -- Do NOT copy any rows, at this stage

and then to populate it with:


INSERT INTO test_backup 
SELECT *
from test AS T
WHERE NOT EXISTS (SELECT * FROM test_backup AS TB WHERE TB.id = T.id)

and to compare with


select * 
from test
where not exists (Select * from test_backup where test.timestamp = test_backup.MyTimestamp)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources