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

Transact-SQL (2005)

Query

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2008-10-02 : 06:48:11

Hi there

It's long day and my brain is not working properly :)

How do I delete the follwoing record from the current #Charges with the following conditions (it needs to have RecID, RetailerID cause there is NO uniqueid)

SELECT RecID, RetailerID FROM #Charges WHERE RetailAmount = 0

=====================

DELETE #Charges FROM #Charges a, (SELECT RecID, RetailerID FROM #Charges WHERE RetailAmount = 0) b
WHERE (a.RecID = b.RecID AND a.RetailerID = b.RecID) AND a.RetailAmount = 0

=====================

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 06:50:54

no need of join. just use
DELETE FROM #Charges WHERE RetailAmount = 0

dewacorp.alliances

452 Posts

Posted - 2008-10-02 : 06:53:59

quote:
Originally posted by visakh16

no need of join. just use
DELETE FROM #Charges WHERE RetailAmount = 0

Sorry ... I need to have that condition met though. Cause there are some duplicate on the RetailerID that is not always RetailAmount = 0 but if one of the record has one. It needs to delete all record associated with RetailerID.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 06:57:24

[code]DELETE c
FROM #Charges AS c
WHERE EXISTS (SELECT * FROM #Charges AS y WHERE y.RetailerID = c.RetailerID AND y.RetailAmount = 0)[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 06:57:52

[code]DELETE c
FROM #Charges AS c
INNER JOIN #Charges AS y ON y.RetailerID = c.RetailerID
WHERE y.RetailAmount = 0[/code]

E 12°55'05.63"
N 56°04'39.26"

dewacorp.alliances

452 Posts

Posted - 2008-10-02 : 06:59:05

quote:
Originally posted by Peso

DELETE c
FROM #Charges AS c
INNER JOIN #Charges AS y ON y.RetailerID = c.RetailerID
WHERE y.RetailAmount = 0

E 12°55'05.63"
N 56°04'39.26"

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 06:59:24

quote:
Originally posted by dewacorp.alliances

quote:
Originally posted by visakh16

no need of join. just use
DELETE FROM #Charges WHERE RetailAmount = 0

Sorry ... I need to have that condition met though. Cause there are some duplicate on the RetailerID that is not always RetailAmount = 0 but if one of the record has one. It needs to delete all record associated with RetailerID.

DELETE c FROM #Charges c 
INNER JOIN(SELECT RetailerID 
FROM #Charges 
GROUP BY RetailerID 
HAVING SUM(CASE WHEN RetailAmount = 0 THEN 1 ELSE 0 END)>0
)tmp
ON c.RetailerID=tmp.RetailerID

dewacorp.alliances

452 Posts

Posted - 2008-10-02 : 07:03:56

quote:
Originally posted by Peso

DELETE c
FROM #Charges AS c
INNER JOIN #Charges AS y ON y.RetailerID = c.RetailerID
WHERE y.RetailAmount = 0

E 12°55'05.63"
N 56°04'39.26"

This is revised answer:

DELETE c
FROM #Charges AS c
INNER JOIN #Charges AS y ON c.RecID = y.RecID AND y.RetailerID = c.RetailerID
WHERE y.RetailAmount = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 07:03:57

quote:
Originally posted by Peso

DELETE c
FROM #Charges AS c
INNER JOIN #Charges AS y ON y.RetailerID = c.RetailerID
WHERE y.RetailAmount = 0

E 12°55'05.63"
N 56°04'39.26"

this will delete only record for retailer which has retailamount 0. but OPs requirement is to delete all lines associated to Retailer if any one of them has 0 amount

Cause there are some duplicate on the RetailerID that is not always RetailAmount = 0 but if one of the record has one. It needs to delete all record associated with RetailerID.

dewacorp.alliances

452 Posts

Posted - 2008-10-02 : 07:05:45

Hi Visak16

Here's the revision of answer:

DELETE c
FROM #Charges AS c
INNER JOIN #Charges AS y ON c.RecID = y.RecID AND y.RetailerID = c.RetailerID
WHERE y.RetailAmount = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 07:08:26

quote:
Originally posted by dewacorp.alliances

Hi Visak16

Here's the revision of answer:

DELETE c
FROM #Charges AS c
INNER JOIN #Charges AS y ON c.RecID = y.RecID AND y.RetailerID = c.RetailerID
WHERE y.RetailAmount = 0

but still wont this delete only record with 0 retail amount?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 07:08:45

quote:
Originally posted by visakh16

this will delete only record for retailer which has retailamount 0. but OPs requirement is to delete all lines associated to Retailer if any one of them has 0 amount

Cause there are some duplicate on the RetailerID that is not always RetailAmount = 0 but if one of the record has one. It needs to delete all record associated with RetailerID.

Nothing beats an example...

DECLARE	@Sample TABLE
	(
		RetailerID INT,
		RecID INT,
		RetailAmount INT
	)

INSERT	@Sample
SELECT	1, 9, 1 UNION ALL
SELECT	1, 9, 0 UNION ALL
SELECT	1, 8, 1 UNION ALL
SELECT	1, 7, 0 UNION ALL
SELECT	2, 1, 0 UNION ALL
SELECT	2, 2, 0 UNION ALL
SELECT	3, 6, 1 UNION ALL
SELECT	3, 8, 1 UNION ALL

SELECT	*
FROM	@Sample

DELETE		c
FROM		@Sample AS c
INNER JOIN	@Sample AS y ON y.RetailerID = c.RetailerID
			AND y.RecID = c.RecID
WHERE		y.RetailAmount = 0

SELECT	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 07:13:13

quote:
Originally posted by visakh16

but still wont this delete only record with 0 retail amount?

No. RecID is not primary key.
It seems to be more to have a grouping value (sort of orderID) for the table.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 07:13:26

Yup..that clears it. what i missed was alias on where. i thought you're looking for first tables retail amount. sorry for the confusion.

dewacorp.alliances

452 Posts

Posted - 2008-10-02 : 07:47:29

quote:
Originally posted by visakh16

Yup..that clears it. what i missed was alias on where. i thought you're looking for first tables retail amount. sorry for the confusion.

Thank you anyway ... sorry for the confusion too :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 07:48:41

quote:
Originally posted by dewacorp.alliances

quote:
Originally posted by visakh16

Yup..that clears it. what i missed was alias on where. i thought you're looking for first tables retail amount. sorry for the confusion.

Thank you anyway ... sorry for the confusion too :)

no probs

i started it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources