delete problem - 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)

delete problem

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-11 : 18:23:34

I have these 2 tables:
table1
ICN Group Cycle File CreationDate
111 1 3/10/2004 file1.xml 1/10/2004
111 2 3/10/2004 file2.xml 1/10/2004
111 3 3/10/2004 file3.xml 2/10/2004

table2
ICN File DownloadDate User
111 file1.xml 3/12/2004 joe
111 file1.xml 3/15/2004 dan
111 file2.xml 4/1/2004 ben

What I want to do is delete all files from table1 and 2 with table1.creationdate > 60 days. Filename in table1 is always unique, but not in table2.

After the query,

table 1
111 3 3/10/2004 file3.xml 2/10/2004

table2
- empty

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-11 : 18:28:19

DELETE FROM Table2
FROM Table2 t2
INNER JOIN Table1 t1
ON t1.ICN = t2.ICN
WHERE t1.CreateDate < GETDATE() - 60

DELETE FROM Table1
WHERE CreateDate < GETDATE() - 60

I don't understand what you mean by table1.creationdate > 60 days, so please explain further. It would also help if you provided the DDL for the tables as well as INSERT INTO statements for your sample data.

Tara

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-12 : 12:21:11

Thanks Tara. I worked with what you suggested and I got it to work. I used DATEDIFF to determine whether the creation date was over 60 days ago.

I have another problem here though.

CREATE TABLE temp1
(ICN varchar(12),
UID varchar(12),
GroupNum int,
Cycle datetime,
Downloaded datetime NULL,
FName varchar(50)
)

INSERT INTO temp1
VALUES('111', 'joe', 1, '1/12/2003', '1/15/2003', 'file1')

INSERT INTO temp1
VALUES('111', 'joe', 1, '1/12/2003', NULL, 'file1')

INSERT INTO temp1
VALUES('111', 'joe', 2, '1/12/2003', '1/15/2003', 'file2')

INSERT INTO temp1
VALUES('111', 'joe', 3, '1/12/2003', NULL, 'file3')

--DROP TABLE temp1

Note that the first and second records are almost identical except their Downloaded field values. What I want to do is delete the second record with Downloaded IS NULL. Pls help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-12 : 12:30:28

DELETE FROM temp1
WHERE Downloaded IS NULL AND...

Maybe I'm missing something. I don't think I fully understand what you want.

Tara

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-12 : 12:43:00

I couldn't do that because there are other records where Downloaded IS NOT NULL (record 4) and I don't want to delete it. Basically record 2 is a duplicate of record 1, so I want to delete it. I want to leave the first record with Downloaded = NOT NULL because it means that this user has already downloaded the file and I don't want him to download it again.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-12 : 12:57:46

DELETE t1
FROM temp1 t1
INNER JOIN temp1 t2 ON t1.ICN = t2.ICN
AND t1.UID = t2.UID
AND t1.GroupNum = t2.GroupNum
AND t1.Cycle = t2.Cycle
AND t1.FName = t2.FName
WHERE
t1.Downloaded IS NULL

This might be slow. It's basically looking for matched recordsets in temp1. It will delete matched rows where only the Downloaded of the matches = NULL.

Is that what you want???

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-12 : 13:04:01

Here ya go:



DELETE temp1
FROM temp1 t1
INNER JOIN 
	(
		SELECT ICN, UID, GroupNum, Cycle, FName, COUNT(*) AS COUNT
		FROM temp1
		GROUP BY ICN, UID, GroupNum, Cycle, FName
		HAVING COUNT(*) > 1
	) t2
ON t1.ICN = t2.ICN AND t1.UID = t2.UID AND t1.GroupNum = t2.GroupNum AND t1.Cycle = t2.Cycle AND t1.FName = t2.FName
WHERE t1.Downloaded IS NULL

The joined table shows me where the duplicates are.

Tara

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-15 : 11:02:23

This gave me an error "The column prefix 't2' does not match with a table name or alias name used in the query.". I tried fixing it but nothing works. Pls help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-15 : 11:21:26

did you alias the derived query as "t2", as in Tara's code ?

- Jeff

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-15 : 11:33:01

Yes, I did.

I tried running the select query by itself and it worked fine, but when running both the queries as one I got the error.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-15 : 12:00:36

post what you are trying to execute that is giving you the error.

- Jeff

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-15 : 12:10:39

Jeff, here it is.

DELETE temp1
FROM temp1 t1
INNER JOIN
(
SELECT ICN, UID, GroupNum, Cycle, FName, COUNT(*)
FROM temp1
GROUP BY ICN, UID, GroupNum, Cycle, FName
HAVING COUNT(*) > 1
) t2
ON t1.ICN = t2.ICN AND t1.UID = t2.UID AND t1.GroupNum = t2.GroupNum AND t1.Cycle = t2.Cycle AND t1.FName = t2.FName
WHERE t1.Downloaded IS NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-15 : 12:21:15

Run this script as is (do not modify anything):



SET NOCOUNT ON

CREATE TABLE temp1
(ICN varchar(12),
UID varchar(12),
GroupNum int,
Cycle datetime,
Downloaded datetime NULL,
FName varchar(50)
)

INSERT INTO temp1
VALUES('111', 'joe', 1, '1/12/2003', '1/15/2003', 'file1')

INSERT INTO temp1
VALUES('111', 'joe', 1, '1/12/2003', NULL, 'file1')

INSERT INTO temp1
VALUES('111', 'joe', 2, '1/12/2003', '1/15/2003', 'file2')

INSERT INTO temp1
VALUES('111', 'joe', 3, '1/12/2003', NULL, 'file3')

SELECT * FROM temp1

DELETE temp1
FROM temp1 t1
INNER JOIN 
(
SELECT ICN, UID, GroupNum, Cycle, FName, COUNT(*) AS [Count]
FROM temp1
GROUP BY ICN, UID, GroupNum, Cycle, FName
HAVING COUNT(*) > 1
) t2
ON t1.ICN = t2.ICN AND t1.UID = t2.UID AND t1.GroupNum = t2.GroupNum AND t1.Cycle = t2.Cycle AND t1.FName = t2.FName
WHERE t1.Downloaded IS NULL

SELECT * FROM temp1

DROP TABLE temp1

Does it run without errors? It runs without errors for me.

Tara

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-15 : 12:31:06

I still am getting the error message (5 times). I know this is weird because the syntax looks fine to me too. Could it have something to do with the database settings? I'm using SQL Server 7.0.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-15 : 12:33:50

I am using SQL 2000. I changed my database compatibility mode to 70 and it still works for me. I even tried 65 and it still works. Do you have another box to try this out on?

Tara

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-15 : 12:40:51

I changed the statement Delete temp1 to Delete t1 and it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources