comparing rows

Is there any way of comparing specific rows in a table? i.e. I want to delete one of two rows in a table (that also has more rows in it other than the 2 i want to compare). Basicaly, each of the rows have a date field and a unique idtntifier, I want to compare two rows against each other using the unique indetifier and delete the one with the earliest date.

Anyone know how this can be done?

appreciated.......Please send sample script that create this two tables and inset couple of rows. This will be the best way to help you.

Eyal|||as the term unique identifier already states, it is unique. This means that you can't compare on that field. Isn't there another 'identifying' column (a foreign key)? Otherwise, if all columns except the identifier and date compare all columns to each other.

Compare timestamps then delete

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

Quote:

Originally Posted by JReneau35

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

delete ... where datediff(mm,datefield,getdate()) > 6|||

Quote:

Originally Posted by ck9663

delete ... where datediff(mm,datefield,getdate()) > 6

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.|||

Quote:

Originally Posted by JReneau35

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.

datediff() gets the difference between the start data and end date. the "mm" signifies you're trying to get the difference expressed in number of months. getdate() is a function that returns the system date.

essentially, you're deleting the record if the difference between the datefield (content of your field) and the system date is more then 6 months ... if you need to include 6 months and older, do a "=>" instead

Compare Script

I need to write a compare script to compare the data that two databases may have in common and to delete the records in the first database. And the records are are not incommon to be moved over to the second database.
Thanks
Lystrahttp://www.sqlscripter.com/|||Thanks, but theres resistions on my PC

I was able to write this:

Select ClientHost,Username,
Logtime,Service, Machine, ServerIP
,ProcessingTime, BytesRecvd,BytesSent,
ServiceStatus, Win32Status, Operation,
Target, Parameters, Department
From IISLOG.dbo.IISLOG as a where NOT EXISTS
(select ClientHost,Username,
Logtime,Service, Machine, ServerIP
,ProcessingTime, BytesRecvd,BytesSent,
ServiceStatus, Win32Status, Operation,
Target, Parameters, Department
from IISLOG as b
Where a.ClientHost =b.ClientHost
and a.Username = b.Username
and a.Logtime = b.Logtime
and a.Service = b.Service
and a.Machine = b.Machine
and a.ServerIP = b.ServerIP
and a.ProcessingTime = b.ProcessingTime
and a.BytesRecvd = b.BytesRecvd
and a.BytesSent = b.BytesSent
and a.serviceStatus = b.ServiceStatus
and a.Win32Status = b.Win32Status
and a.Operation = b.Operation
and a.Target = b.Target
and a.Parameters = b.Parameters
and a.Department = b.Department)|||Don't you have a primary key?|||That's the problem because once the data is archive the Id which is the primary key is not archive. But it does have a ID field in the second database. It kind of like ID autonumber in MS access.

This is the code it uses:

CREATE PROCEDURE sp_archiveLOG AS
declare @.Today varchar(10)
select @.Today = convert(varchar(10),getdate(),101)
--select @.Today = '2002-03-30'
/*
copy updated rows to archive database
*/
insert IISLOG_ARCHIVE2004..iislog
select clienthost,username,logtime,service,machine,server ip,processingtime,bytesrecvd,bytessent,servicestat us,win32status,operation,target,parameters,departm ent from IISLOG..iislog
where department is not null
and ( LogTime >= DateAdd(day, -1, @.Today) AND LogTime < @.Today )
GO

As you see it doesn't archive the id number

The next code deletes the whole record

CREATE PROCEDURE sp_cleanupLOG AS
declare @.Today varchar(10)
select @.Today = convert(varchar(10),getdate(),101)
--select @.Today = '2002-03-30'
/*
delete rows from current iislog table
*/
delete from IISLOG..iislog
where ( LogTime >= DateAdd(day, -1, @.Today) AND LogTime < @.Today )

So for me to compare I would have to compare the whole field.

Lystra

Compare fields in different tables

I need to compare filed A in Table A to Field B in Table B. I then need to
delete any records in Table A that don't exist in Table B. I ran the
following Minus query to show the different records, just waht to make sure
I have the correct syntax to delete these recors.
select ID from tableB
MINUS
select ID from tableA
Thanks.
BenHi,
Keyword MINUS can be used in Oracle. AFAIK, MINUS wil not serve the
comparison between 2 tables in SQL Server.
You need to use either NOT EXISTS or NOT IN in SQL Server for comparison.
SELECT t1.Col1 FROM Table1 AS t1
WHERE NOT EXISTS( SELECT * FROM Table2 AS t2
WHERE t2.Col1 = t1.Col1 )
or:
SELECT t1.Col1 FROM Table1 AS t1
WHERE t1.Col1 NOT IN( SELECT t2.Col1 FROM Table2 AS t2 )
Thanks
Hari
SQL Server MVP
"Ben" <benNOSPAMhough@.comcast.net> wrote in message
news:uMp89G6lFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I need to compare filed A in Table A to Field B in Table B. I then need to
>delete any records in Table A that don't exist in Table B. I ran the
>following Minus query to show the different records, just waht to make sure
>I have the correct syntax to delete these recors.
> select ID from tableB
> MINUS
> select ID from tableA
> Thanks.
> Ben
>|||SQL Server doesn't support a MINUS operator.
Try the following (untested):
DELETE FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE TableB.b = TableA.a)
--
David Portas
SQL Server MVP
--

compacting LOG file

how can you compact .log file ?
they can be > 20 gigas ! ...
can you delete it ?do you mean the log files that the server itself writes? Those have no file extension normally, usuall called something like ERRORLOG. when the server restarts it renames it to ERRORLOG.1 and starts a new one. I would be very surprised if you have a 20gb one though. that's pretty large. maybe you have a lot of failed login attempts (which are logged as I recall).

Or perhaps you mean a ldf file? These are transaction log files for a database - it's common for these to get large. You can shrink the size of the ldf using DBCC SHRINKFILE. You should not delete an ldf file unless you want to delete the database it's associated with.

Note that if you care about recoverability, you should be making regular backups of both your database and transaction log.|||thank you Jezemine

yes of course I mean transaction log files
DBCC SHRINKFILE ? i musyt look how to use it ? do you run it as a stored procedure ?

EXECUTE DBCC SHRINKFILE

thank you for helping|||it's not a sproc.

read about DBCC SHRINKFILE in bol.

Commit Help

I might not be explaining this correct. I have a table right now that has 7
rows. I want to delete 1 of the rows. Then I want to go back and do the
rollback to undo the transaction of the delete. If it works the way I
understand, I should do a select statement and see the 7 rows. Delete the 1
I want. Do a select statement and see 6 rows. Then do the rollback. Then
another select statement and see the original 7 rows again. I can't do
something that gives me errors because I know I will get none. I know the
delete I am doing works. The issue is doing a delete and then a rollback to
obtain the information that I started with before I did the delete.
I can't do a stored procedure. I also can't do an else if program that
gives errors. All I want is a basic way to delete a row. Then view the
table to make sure it deleted, then go back and do the rollback command and
then view the table again to make sure that the original information is
there before I did the original delete.
Message posted via http://www.webservertalk.comNot sure I understand what you want, but, use QueryAnalyzer, then execute
the SQL exacyl like you described but wrapped in a begin transaction like so
begin transaction
select ...
delete...
select...
rollback...
end
JIM
"tina miller via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:b2387b603d094ec684efe5f85f1c30ef@.SQ
webservertalk.com...
>I might not be explaining this correct. I have a table right now that has 7
> rows. I want to delete 1 of the rows. Then I want to go back and do the
> rollback to undo the transaction of the delete. If it works the way I
> understand, I should do a select statement and see the 7 rows. Delete the
> 1
> I want. Do a select statement and see 6 rows. Then do the rollback. Then
> another select statement and see the original 7 rows again. I can't do
> something that gives me errors because I know I will get none. I know the
> delete I am doing works. The issue is doing a delete and then a rollback
> to
> obtain the information that I started with before I did the delete.
> I can't do a stored procedure. I also can't do an else if program that
> gives errors. All I want is a basic way to delete a row. Then view the
> table to make sure it deleted, then go back and do the rollback command
> and
> then view the table again to make sure that the original information is
> there before I did the original delete.
> --
> Message posted via http://www.webservertalk.com|||Read your previous post.
AMB
"tina miller via webservertalk.com" wrote:

> I might not be explaining this correct. I have a table right now that has
7
> rows. I want to delete 1 of the rows. Then I want to go back and do the
> rollback to undo the transaction of the delete. If it works the way I
> understand, I should do a select statement and see the 7 rows. Delete the
1
> I want. Do a select statement and see 6 rows. Then do the rollback. Then
> another select statement and see the original 7 rows again. I can't do
> something that gives me errors because I know I will get none. I know the
> delete I am doing works. The issue is doing a delete and then a rollback t
o
> obtain the information that I started with before I did the delete.
> I can't do a stored procedure. I also can't do an else if program that
> gives errors. All I want is a basic way to delete a row. Then view the
> table to make sure it deleted, then go back and do the rollback command an
d
> then view the table again to make sure that the original information is
> there before I did the original delete.
> --
> Message posted via http://www.webservertalk.com
>

Commit & Rollback Logic in VB.NET

I have several sets of code that need to delete rows from more than one database at a time. The rows are basically linked without being identified as having a foreign key. This means I issue two deletes. If one fails, especially the second one, there is no way to roll the first delete back.

Can someone either point me to some code that enables me to link the deletions, allowing me to insure that both are successful or both do not occur.

I cannot identify any fields on the secondary database table as specifically linked to the primary, as the secondary database is a storage medium for images, that may be linked to more than one different table.

TIA for any opinions, options, etc. Tom

you need to wrap a transaction around your deletes

read this:http://msdn.microsoft.com/msdnmag/issues/06/11/DataPoints/default.aspx

|||

Many thanks. That looks like the ticket.

Tom