June 4, 2003 at 1:11 pm
hi,
If i had a table ORDERS and have few columns in it. Suppose there are 2 rows that have same data execpt for the date field.Two columns order_no and line_no. Based on a date range how do i delete the older rows and keep the new rows? e.g.
Order_No Line_No Ship_Date
123 1 5/1/03
123 2 5/1/03
123 1 5/31/03
My final result should look like this
Order_No Line_No Ship_Date
123 2 5/1/03
123 1 5/31/03
what should i do? Please help
June 4, 2003 at 2:02 pm
One option is to use a derived table containing the key + max(ship_date) and then delete from your table the rows that don't match as so:
DELETE Orders
--select *
FROM Orders t
LEFT OUTER
JOIN (SELECT Order_No ,Line_No, Ship_Date = MAX(Ship_Date) FROM Orders GROUP BY Order_No ,Line_No) s
ON t.Order_No = s.Order_No
AND t.Line_No = s.Line_No
AND t.Ship_Date = s.Ship_Date
WHERE s.Ship_Date IS NULL
June 4, 2003 at 2:05 pm
Try:
DELETE FROM Orders
FROM Orders
INNER JOIN
(
SELECT Order_No, Line_No, MAX(Ship_Date) as MaxShipDate
FROM Orders
GROUP BY Order_No, Line_No
) AS dt
ON Orders.Order_No = dt.Order_No
AND Orders.Line_No = dt.Line_No
AND Orders.Ship_Date <> dt.MaxShipDate
June 5, 2003 at 12:10 pm
hey Jpipes, i tried the query. It works fine with Select statement, however, when i use the delete statement it gives me an error at the INNER JOIN and at the AS commands. Let me know if i can workaround this.
June 5, 2003 at 12:24 pm
satish,
the AS is technically optional, so you can remove it if you want. Other than that, my colleague and I can't see anything wrong with it. However, to make the query more efficient, I suppose the last line doesn't need to be:
AND Orders.Ship_Date <> dt.MaxShipDate.
It could be better written:
AND Orders.Ship_Date < dt.MaxShipDate
Hope you find it,
Jay
ps: what version of SQL are you running?
June 5, 2003 at 12:34 pm
Jay, i am using SQL server 7.0
Here's the query and the error results:
DELETE
FROM WTRDET
INNER JOIN
(
SELECT Order_NBR ,Line_NBR, MAX(Ship_Date) as MaxShipDate
FROM WTRDET GROUP BY Order_NBR ,Line_NBR
) AS dt
ON WTRDET.Order_NBR = dt.Order_NBR
AND WTRDET.Line_NBR = dt.Line_NBR
AND WTRDET.Ship_Date < dt.MaxShipDate
Error result:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'INNER'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AS'.
June 5, 2003 at 12:41 pm
satish,
Take a closer look at my earlier query. The DELETE FROM Orders FROM Orders was actually not a typo, although I admit that the SQL syntax in this situation is extremely confusing. Here's your query:
DELETE FROM WTRDET
FROM WTRDET
INNER JOIN
(
SELECT Order_NBR ,Line_NBR, MAX(Ship_Date) as MaxShipDate
FROM WTRDET GROUP BY Order_NBR ,Line_NBR
) AS dt
ON WTRDET.Order_NBR = dt.Order_NBR
AND WTRDET.Line_NBR = dt.Line_NBR
AND WTRDET.Ship_Date < dt.MaxShipDate
June 5, 2003 at 12:49 pm
Thanks Jay it worked with the double FROM WTRDET statement. Its strange though while i was testing SQL with the same SELECT * query it gave me an error. But worked fine with the DELETE STATEMENT. Thanks once again for your time and help.
March 1, 2006 at 10:11 pm
SQL Champs , I have Doubts pertaining to DELETE command to be used for a specific scenario :-
example # 1
Actually - Consider this data in the Table Salary_Mst
Name Salary
Muthu K 460000
Muthu K 460000
Now, My requirement is to delete the duplicate record only - that means after i write a sample Delete command & execute it, What must happen is that 1 record gets Deleted & one is Retained.
I would find it feasible if it had been something like :-
ID Name Salary
1 Muthu K 460000
2 Muthu K 460000
bcoz then we can use the WHERE clause & write a simpler delete command
Delete From Salary_Mst where ID = 2
example # 2
If we have data in table Employee_Mst like this :-
EmpName EmpDesignation
Muthu K ITA
Muthu K ITA
Muthu K ITA
Muthu K ITA
Sankara ASE
Sankara ASE
Sankara ASE
Sankara ASE
Now i need a Query which will leave the data in this table such :-
EmpName EmpDesignation
Muthu K ITA
Sankara ASE
Can any1 help me in this regard ???
Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services
March 3, 2006 at 12:08 am
Would it be easy to :
- select distinct and store in temp table
- delete the table
- re-insert back
- Just pondering - That would mean - more than 1 Query will be written ?!
--- Pls. Advise
Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply