How to delete this information using T-SQL

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

Transact-SQL (2000)

How to delete this information using T-SQL

Author

Topic

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 15:55:20

I have a table of SY_GFC that contains GFC_NO and CURR_AMT. I am trying to write a script that deletes the GFC_NO and CURR_AMT if the CURR_AMT is equal to 0.00.

GFC_NO CURR_AMT ORIG_AMT
123456 25.00 50.00
1234567 0.00 20.00
12345678 2.00 10.00
245617 10.00 20.00

GFC_NO equals Gift Card Number.
CURR_AMT equals the current amount on the Gift Card.

So basically when someone has used up all of their balance I want to be able to reuse the card number and add another amount on it, but in order to do so the record of the card number and the balance must be deleted.

I would need some way to declare the GFC_NO as a variable so the script will delete any card number that has a zero balance.

Thanks!

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-12-18 : 16:01:14

I swear this smells like homework as it's so very basic in concept. Can you show what you've attempted? Have you looked at delete in BOL? Why would you need to declare GFC_NO as a variable, for what purpose? Show us your effort and we'll show you where your issue(s) are.

Terry

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 16:22:01

Homework? This is not a school project if thats what you are asking.

I have tried the following...

DELETE GFC_NO, ORIG_AMT, CURR_AMT FROM SY_GFC
WHERE ORIG_AMT='0.00'

However, I need this to be a stored procedure so after the ticket sale it will check the CURR_AMT on the GFC_NO and then run the procedure.

@GFC_NO T_CR_CARD_NO Just as FYI

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 16:26:38

I was thinking to basically run a trigger that was a select statement from the DB to get the CURR_AMT and if it was equal to 0.00 then run the stored procedure.

Something like...

Select CURR_AMT, GFC_NO, FROM SY_GFC
where CURR_AMT='0.00'
if CURR_AMT='0.00' then run PHSA.USP_PURGE_GC

Something like that. Sorry I am not a programmer so my syntax could be way out of wack.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-12-18 : 16:28:30

Should be simply: delete SY_GFC where ORIG_AMT = 0

I'm still not quite clear on the variable thing. Are you only going to be potentially deleting one row at a time based on the variable or all where amount = 0?

Terry

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 16:31:06

I want to delete all where the amount equals zero...

So for example..

If I have the following...

GFC_NO CURR_AMT ORIG_AMT
123456 25.00 50.00
1234567 0.00 20.00
12345678 2.00 10.00
245617 10.00 20.00

The only column I want to delete would be the one where the CURR_AMT='0' and the rest stay intact because they still have a balace on their card.

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 16:32:28

Any shouldn't it be something like delete SYC_GFC where CURR_AMT='0'

?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-12-18 : 16:35:30

quote:
Originally posted by silvershark

Any shouldn't it be something like delete SYC_GFC where CURR_AMT='0'

?

All depends on your column defintion. If it's dec or money it should be delete SYC_GFC where CURR_AMT= 0 This will delete ALL rows where the amount = 0.

Terry

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 16:39:06

Ok, thats what I thought, it would just delete the row... I dont want to delete the row.. I want to delete the corresponding column that goes with that GFC_NO.

IE: GFC_NO='1000' CURR_AMT='0.00'

So a table holds things like originial amount, current amount, gift card number, selling description, expiration date, blah blah blah. I only want to delete all that information if the current amount equals 0.

Then I have a foreign key constraight that points to table SY_GFC_ACTIV and points to the GFC_NO.

So I would have to delete the SY_GFC_ACTIV first before deleting anything in the SYC_GFC table.

I know this makes things complicated and hopefully I explained everything right.

Thanks again for all your help.

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 16:47:08

OK, so I was confused. The delete SY_GFC where CURR_AMT='0' worked great! However, I had to first go into SY_ACTIV_GFC first and delete the rows with the GFC_NO of 1000 that corresponded with the gift card number 1000 that had zero dollars in table SY_GFC.

That is as clear as I can be.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-12-18 : 16:50:02

Do you want to delete or update the remaining columns and retain the recor og the GFC_NO but no additional information?

This will retain the parent record (not sure why you'd want to do that??):
delete all FK constraints:
delete SY_GFC_ACTIV where SY_CFG_ACTIV.GFC_NO in (select GFC_NO from SYC_GFC where CURR_AMT = 0)
update record, retaining the parent:
Update SYC_GFC set CURR_AMT = 0, fielda = null, .etc. WHERE ORIG_AMT = 0

Otherwise:

delete all FK constraints:
delete SY_GFC_ACTIV where SY_CFG_ACTIV.GFC_NO in (select GFC_NO from SYC_GFC where CURR_AMT = 0)
then, delete all parent records:
delete SYC_GFC where CURR_AMT= 0

Terry

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-12-18 : 16:51:49

We're posting against each other. Try this:

delete all FK constraints:
delete SY_GFC_ACTIV where SY_CFG_ACTIV.GFC_NO in (select GFC_NO from SYC_GFC where CURR_AMT = 0)
then, delete all parent records:
delete SYC_GFC where CURR_AMT= 0

Terry

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 17:00:19

You my friend are awesome!

Here is the finished code that worked.

delete SY_GFC_ACTIV where SY_GFC_ACTIV.GFC_NO in (select GFC_NO from SY_GFC where CURR_AMT = 0.00)
delete SY_GFC where CURR_AMT= 0.00

Now, how would I make that a stored procedure so I could run it after something?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-12-18 : 17:07:57

CREATE PROCEDURE dbo.uspDeleteZeroCurrentBalances

AS
BEGIN

delete SY_GFC_ACTIV where SY_GFC_ACTIV.GFC_NO in (select GFC_NO from SY_GFC where CURR_AMT = 0.00)
delete SY_GFC where CURR_AMT= 0.00

end
go

Edit - then simply: exec uspDeleteZeroCurrentBalances

Terry

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 17:09:11

This is what I have tried. But I get an error.

/* USP_PURGE_INACTIVE_GC */

CREATE procedure dbo.USP_PURGE_INACTIVE_GC

delete SY_GFC_ACTIV where SY_GFC_ACTIV.GFC_NO in (select GFC_NO from SY_GFC where CURR_AMT = 0.00)
delete SY_GFC where CURR_AMT= 0.00

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 17:12:11

I cant thank you enough!

silvershark
Starting Member

48 Posts

Posted - 2008-12-18 : 18:02:49

If I was to use this same idea but make it a trigger... Would it delete automatically when it sees CURR_AMT as 0?

That's ideally what I am looking for.

silvershark
Starting Member

48 Posts

Posted - 2008-12-19 : 17:16:32

CREATE trigger deletegiftcard on dbo.SY_GFC
FOR INSERT, UPDATE, DELETE
AS
IF CURR_AMT='0'
BEGIN
delete SY_GFC_ACTIV where SY_GFC_ACTIV.GFC_NO in (select GFC_NO from SY_GFC where CURR_AMT = 0.00)
delete SY_GFC where CURR_AMT= 0.00
END
GO

It is stating that the column CURR_AMT does not exist... but when I do a select CURR_AMT from SY_GFC it shows me the row information.

silvershark
Starting Member

48 Posts

Posted - 2008-12-19 : 17:19:43

Ahh, it accepted this...

CREATE TRIGGER deletegiftcard on SY_GFC
FOR INSERT, DELETE, UPDATE
AS
BEGIN
delete SY_GFC_ACTIV where SY_GFC_ACTIV.GFC_NO in (select GFC_NO from SY_GFC where CURR_AMT = 0.00)
delete SY_GFC where CURR_AMT= 0.00
end
go

silvershark
Starting Member

48 Posts

Posted - 2008-12-23 : 12:34:40

Ok, so the trigger would work fine, but I keep getting an error message saying the GIFT_NO is locked. Is there a way to delay the trigger to run, like in vbs or other programming languages you can specify a WAIT. Can you do the same thing in SQL? IE:

CREATE trigger deletegiftcards on dbo.SY_GFC_HIST
FOR INSERT, UPDATE, DELETE
AS
BEGIN
WAIT 500000 then
delete SY_GFC_ACTIV where SY_GFC_ACTIV.GFC_NO in (select GFC_NO from SY_GFC where CURR_AMT = 0.00)
delete SY_GFC where CURR_AMT= 0.00
end

Thanks. Basically it would work better as a stored procedure, but I have no way to call it after the transaction is completed. So the best solution I have come up with so far is to add a Job to the SQL server to do the following every 1 minute.

delete SY_GFC_ACTIV where SY_GFC_ACTIV.GFC_NO in (select GFC_NO from SY_GFC where CURR_AMT = 0.00)
delete SY_GFC where CURR_AMT= 0.00

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-23 : 12:59:38

You could use WAITFOR, however you shouldn't do that type of thing in a trigger. Could you post the exact error message that you get?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources