The script:
delete From InvoiceItems Where InvoiceID In
(select invoiceid from invoices
where type = 'Credit Card' and date = "Nov 13 1998
12:00AM"
)
delete from ExternalTrans Where InvoiceID In
(select invoiceid from invoices
where type = 'Credit Card' and date = "Nov 13 1998
12:00AM"
)
delete from invoices
where type = 'Credit Card' and date = "Nov 13 1998 12:00AM"
The output:
Msg 547, Level 16, State 2
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK__Charges__ItemID__5A6472D4'. The conflict occurred in database
'Emerald', table 'Charges', column 'ItemID'
Command has been aborted.
(0 row(s) affected)
Msg 547, Level 16, State 2
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK__InvoiceIt__Invoi__52C3510C'. The conflict occurred in database
'Emerald', table 'InvoiceItems', column 'InvoiceID'
Command has been aborted.
The 0 row(s) was from externaltrans where I alreadyy deleted the data
last night. So I need to delete the charges in the charges table too?
> -----Original Message-----
> From: Dale E. Reed Jr. [SMTP:daler@iea-software.com]
> Sent: Friday, December 04, 1998 6:47 PM
> To: emerald@iea-software.com
> Subject: Re: [Emerald] Dropping data from a table
>
> Brandon Bryant wrote:
> >
> > Interesting problem. I just changed my billing cycle from 30-30 to
> 1-1.
> > Unfortunately, there was a credit card batch that had been
> generated
> > before I did that, and of course it's wrong. What I want to do is
> delete
> > the invoices and externaltrans stuff for those. I did a
> > select * from invoices where type = "Credit Card" and date = "Nov 13
> 1998
> > 12:00AM"
> > select * from externaltrans where reqdate > "Nov 12 1998 12:00AM"
> >
> > And I got a list of (I think) all the data I need to remove. If I
> remove
> > the invoice from these two areas, can I then re-create the batch
> with the
> > new dates and get on with my life? And if so, what is the SQL
> command to
> > delete this data?
>
> Yes you can You need three queries, two of them based on your
> original one.
>
> Delete From InvoiceItems Where InvoiveID In
> (select invoiceid from invoices
> where type = 'Credit Card' and date = '11/1398'
> )
>
> Delete From ExternalTrans Where InvoiveID In
> (select invoiceid from invoices
> where type = 'Credit Card' and date = '11/1398'
> )
>
> Delete from invoices
> where type = 'Credit Card' and date = '11/1398'
>
>
> --
> Dale E. Reed Jr. (daler@iea-software.com)
> _________________________________________________________________
> IEA Software, Inc. | RadiusNT, Emerald, and NT FAQs
> Internet Solutions for Today | http://www.iea-software.com
>
> For more information about this list, including removal,
> please see http://www.iea-software.com/maillist.html