Its Me Again! - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Its Me Again!

Author

Topic

kevinxyx
Starting Member

11 Posts

Posted - 2011-08-15 : 04:09:36

sorry about this, i am really newbie in sql,

i want to delete similar records, in the picture

8/1/2011
8/21/2011
8/22/2011
8/23/2011
8/24/2011

has 0 tag on record

while the same record has 1 tag on it

i want to delete the record with 1 tag the same as the record with 0 tag, how to do this?

Delete Similar Records

quote:

To delete similar records, i.e. where the records are not the same but one field is the same and only one copy needs to be preserved, try the following SQL:

delete T1
from MyTable T1, MyTable T2
where T1.dupField = T2.dupField
and T1.uniqueField > T2.uniqueField
This will delete all records from the table MyTable which have the same value for the field dupField, leaving that record which has the lowest value in uniqueField.

my problem is something like this, quoted from some sql website, problem is cannot understand it, fact that im still learning sql, appreciate any help thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-15 : 05:44:30

Hi. I think I get what you want but can you clarify?

You want to remove:
"Any Row where the [Record] Column = 0 where another row exists with the same [DateLog] values and the [Record] value of that row = 1"

Do you also want to remove (is this even possible -- It shouldn't!):
"Any row that is a complete duplicate of another row (leaving one row behind)"

Here's how you'd do the first one.


DELETE ld
FROM
	LogDate AS ld
WHERE
	ld.[Record] = 0
	AND EXISTS (
		SELECT 1
		FROM LogDate AS ld2
		WHERE ld2.[DateLog] = ld.[DateLog]
		  AND ld2.[Record] = 1
		)

Is there some sort of KEY to this table?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kevinxyx
Starting Member

11 Posts

Posted - 2011-08-15 : 06:37:42

sorry for not making it clear, thanks for your help this one do the trick

DELETE ld
FROM LogDate AS ld
WHERE ld.[Record] = 1 AND EXISTS
(SELECT 0
FROM LogDate AS ld2
WHERE ld2.[DateLog] = ld.[DateLog] AND ld2.[Record] = 0)

it deleted the same record in datelog where the record = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources