Delete from table based on date in another table

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 2005 Forums

Transact-SQL (2005)

Delete from table based on date in another table

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-05-24 : 15:33:51

I want to run a script that deletes records from an order detail record by a date range but does not have a date in it. There is an Order Header table that contains the dates.

Tables:
ORDHDR
ORDDTL

Fields:
ordhdr.ord_no
ordhdr.inv_no
ordhdr.ord_dt

LINKS:
ORD_NO
INV_NO

I want to delete records from both tables where
(ord_dt BETWEEN '2000-01-01 00:00:00' AND '2001-01-01 00:00:00')

Do I have to create two scripts and delete from the orddtl first or can this be put into one script

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 16:05:28

Not tested. Run it in a test environment first.

DELETE a
FROM ORDDTL a
INNER JOIN ORDHDR b on a.ORD_NO = b.ORD_NO and a.INV_NO = b.INV_NO
WHERE b.ORD_DT BETWEEN '2000-01-01 00:00:00' AND '2001-01-01 00:00:00'

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-05-25 : 08:28:23

When I run it, it changes it to this and then errors out

DELETE FROM a
FROM oeordlin_sql AS a INNER JOIN
oeordhdr_sql AS b ON a.ord_type = b.ord_type AND a.ord_no = b.ord_no CROSS JOIN
a
WHERE (b.entered_dt BETWEEN '2000-01-01 00:00:00' AND '2001-01-01 00:00:00')

"INVALID OBJECT NAME 'a'"

Sachin.Nand

2937 Posts

Posted - 2010-05-25 : 08:44:54

quote:
Originally posted by Vack

When I run it, it changes it to this and then errors out

DELETE FROM a
FROM oeordlin_sql AS a INNER JOIN
oeordhdr_sql AS b ON a.ord_type = b.ord_type AND a.ord_no = b.ord_no CROSS JOIN a
WHERE (b.entered_dt BETWEEN '2000-01-01 00:00:00' AND '2001-01-01 00:00:00')

"INVALID OBJECT NAME 'a'"

What is that CROSS JOIN for?

PBUH

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-25 : 10:17:44

Also..what do you mean it "changes" to this? How are you running it? Try running it from SSMS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources