Ask a Question related to ASP Database, Design and Development.
-
Mike #1
How do you delete a record form multiple tables
I have a dbase that has about 30 tables in it. How
can I delete a record out of all of the tables without
doing 30 DELETE statements?
Example:
SQLstmt = "DELETE * FROM fd_1 WHERE CustomerSSN = '111-22-3333'"
Set RS = conn.execute(SQLstmt)
SQLstmt = "DELETE * FROM fd_2 WHERE CustomerSSN = '111-22-3333'"
Set RS = conn.execute(SQLstmt)
SQLstmt = "DELETE * FROM fd_3 WHERE CustomerSSN = '111-22-3333'"
Set RS = conn.execute(SQLstmt)
etc.....
for some reason
SQLstmt = "DELETE * FROM fd_1, fd_2, fd_3 WHERE CustomerSSN =
'111-22-3333'"
Set RS = conn.execute(SQLstmt)
does not work.
Mike
Mike Guest
-
Delete against multiple tables + And / Or
Hi there, a part of my music site exists of three tables: songs -> id, artist_id, song_name lyrics -> id, song_id, lyrics genre -> id,... -
update multiple records in multiple tables from one form
hello I have been trying to run multiple update queries based on the data entered by user. Brief background: I am fetching data from various... -
Can one <form> submit data to multiple tables?
Am I able to use a single form to write data to multiple tables in my database using php/mySQL? -
Submitting multiple record IDs via a form.
I have a problem more to do with style than ability; I can solve this problem in a number of ways, but I'm keen to find the best way of doing... -
ASP form writing to multiple DB tables.
I'm new to Access and ASP pages, but so far I have been doing OK. This one has me stumped. What I am trying to do is have an ASP form with several... -
Ray at #2 Re: How do you delete a record form multiple tables
This is the same concept as your other post where it's a matter of building
a string in a loop and executing a query.
For i = 1 to 30
sSQL = "delete from fd_" & cstr(i) & " where CustomerSSN='111-22-3333'"
conn.Execute sSQL
Next
Note again that it's NOT
SET RS = conn.execute(sSQL)
It's just
conn.Execute sSQL
Why does your database have all these tables like this?
Ray at work
"Mike" <mike4532> wrote in message
news:%23tdQLwOxDHA.1396@TK2MSFTNGP10.phx.gbl...> I have a dbase that has about 30 tables in it. How
> can I delete a record out of all of the tables without
> doing 30 DELETE statements?
>
> Example:
> SQLstmt = "DELETE * FROM fd_1 WHERE CustomerSSN = '111-22-3333'"
> Set RS = conn.execute(SQLstmt)
>
> SQLstmt = "DELETE * FROM fd_2 WHERE CustomerSSN = '111-22-3333'"
> Set RS = conn.execute(SQLstmt)
>
> SQLstmt = "DELETE * FROM fd_3 WHERE CustomerSSN = '111-22-3333'"
> Set RS = conn.execute(SQLstmt)
>
> etc.....
>
>
> for some reason
>
> SQLstmt = "DELETE * FROM fd_1, fd_2, fd_3 WHERE CustomerSSN =
> '111-22-3333'"
> Set RS = conn.execute(SQLstmt)
>
> does not work.
>
> Mike
>
>
Ray at Guest
-
Mike #3 Re: How do you delete a record form multiple tables
I guess that I should of better explained what I need....
Not all of the table are the same name with a dif number at the end.
There are 20 rd_1, rd_2, etc... and there are also 10 other misc named
tables.
So a string loop will not work in this case.
Can you do a: Delete * From ALL Where........
Mike
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:ew73h4OxDHA.2148@TK2MSFTNGP12.phx.gbl...building> This is the same concept as your other post where it's a matter ofCustomerSSN='111-22-3333'"> a string in a loop and executing a query.
>
> For i = 1 to 30
> sSQL = "delete from fd_" & cstr(i) & " where> conn.Execute sSQL
> Next
>
> Note again that it's NOT
> SET RS = conn.execute(sSQL)
> It's just
> conn.Execute sSQL
>
>
> Why does your database have all these tables like this?
>
> Ray at work
>
>
> "Mike" <mike4532> wrote in message
> news:%23tdQLwOxDHA.1396@TK2MSFTNGP10.phx.gbl...>> > I have a dbase that has about 30 tables in it. How
> > can I delete a record out of all of the tables without
> > doing 30 DELETE statements?
> >
> > Example:
> > SQLstmt = "DELETE * FROM fd_1 WHERE CustomerSSN = '111-22-3333'"
> > Set RS = conn.execute(SQLstmt)
> >
> > SQLstmt = "DELETE * FROM fd_2 WHERE CustomerSSN = '111-22-3333'"
> > Set RS = conn.execute(SQLstmt)
> >
> > SQLstmt = "DELETE * FROM fd_3 WHERE CustomerSSN = '111-22-3333'"
> > Set RS = conn.execute(SQLstmt)
> >
> > etc.....
> >
> >
> > for some reason
> >
> > SQLstmt = "DELETE * FROM fd_1, fd_2, fd_3 WHERE CustomerSSN =
> > '111-22-3333'"
> > Set RS = conn.execute(SQLstmt)
> >
> > does not work.
> >
> > Mike
> >
> >
>
Mike Guest
-
Mike #4 Re: How do you delete a record form multiple tables
In a answer to your last question.
We have an Intranet that has an ASP driven site. On that site we input> Why does your database have all these tables like this?
information about our customers.
We do debt elimination and collect the debts of our clients and record
them to a dbase. We have the ability
to collect up to 20 different debts, such as credit cards, dept. store
cards, etc... Not all of our clients have
the same number of debts. I origionally had a big table that had 6
fields about each debt and there were 20
debts. That resulted in a table that was 120 columns. And if a client
only had 5 debts then i was just waisting
space by having the other 15 debts be null. So normalized the table and
broke it into 20 seperate tables, one for
each debt, that only have 6 records in each. Now I only record the
information into a table if the information exists. So if a client only
has 5 debts, i only write to tables 1-5.
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:ew73h4OxDHA.2148@TK2MSFTNGP12.phx.gbl...building> This is the same concept as your other post where it's a matter ofCustomerSSN='111-22-3333'"> a string in a loop and executing a query.
>
> For i = 1 to 30
> sSQL = "delete from fd_" & cstr(i) & " where> conn.Execute sSQL
> Next
>
> Note again that it's NOT
> SET RS = conn.execute(sSQL)
> It's just
> conn.Execute sSQL
>
>
> Why does your database have all these tables like this?
>
> Ray at work
>
>
> "Mike" <mike4532> wrote in message
> news:%23tdQLwOxDHA.1396@TK2MSFTNGP10.phx.gbl...>> > I have a dbase that has about 30 tables in it. How
> > can I delete a record out of all of the tables without
> > doing 30 DELETE statements?
> >
> > Example:
> > SQLstmt = "DELETE * FROM fd_1 WHERE CustomerSSN = '111-22-3333'"
> > Set RS = conn.execute(SQLstmt)
> >
> > SQLstmt = "DELETE * FROM fd_2 WHERE CustomerSSN = '111-22-3333'"
> > Set RS = conn.execute(SQLstmt)
> >
> > SQLstmt = "DELETE * FROM fd_3 WHERE CustomerSSN = '111-22-3333'"
> > Set RS = conn.execute(SQLstmt)
> >
> > etc.....
> >
> >
> > for some reason
> >
> > SQLstmt = "DELETE * FROM fd_1, fd_2, fd_3 WHERE CustomerSSN =
> > '111-22-3333'"
> > Set RS = conn.execute(SQLstmt)
> >
> > does not work.
> >
> > Mike
> >
> >
>
Mike Guest
-
Mark Schupp #5 Re: How do you delete a record form multiple tables
if all debt types use the same (or similar) columns then you should only
have 1 table for debts with a "debt_type" column to distinguish among them.
In answer to "delete * from ALL"... No, delete doesn't work like that.
--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]
"Mike" <mike4532> wrote in message
news:eXizHZPxDHA.2556@TK2MSFTNGP10.phx.gbl...input> In a answer to your last question.
>>> > Why does your database have all these tables like this?
> We have an Intranet that has an ASP driven site. On that site weand> information about our customers.
> We do debt elimination and collect the debts of our clients and record
> them to a dbase. We have the ability
> to collect up to 20 different debts, such as credit cards, dept. store
> cards, etc... Not all of our clients have
> the same number of debts. I origionally had a big table that had 6
> fields about each debt and there were 20
> debts. That resulted in a table that was 120 columns. And if a client
> only had 5 debts then i was just waisting
> space by having the other 15 debts be null. So normalized the tableonly> broke it into 20 seperate tables, one for
> each debt, that only have 6 records in each. Now I only record the
> information into a table if the information exists. So if a client> has 5 debts, i only write to tables 1-5.
>
> "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> news:ew73h4OxDHA.2148@TK2MSFTNGP12.phx.gbl...> building> > This is the same concept as your other post where it's a matter of> CustomerSSN='111-22-3333'"> > a string in a loop and executing a query.
> >
> > For i = 1 to 30
> > sSQL = "delete from fd_" & cstr(i) & " where>> > conn.Execute sSQL
> > Next
> >
> > Note again that it's NOT
> > SET RS = conn.execute(sSQL)
> > It's just
> > conn.Execute sSQL
> >
> >
> > Why does your database have all these tables like this?
> >
> > Ray at work
> >
> >
> > "Mike" <mike4532> wrote in message
> > news:%23tdQLwOxDHA.1396@TK2MSFTNGP10.phx.gbl...> >> > > I have a dbase that has about 30 tables in it. How
> > > can I delete a record out of all of the tables without
> > > doing 30 DELETE statements?
> > >
> > > Example:
> > > SQLstmt = "DELETE * FROM fd_1 WHERE CustomerSSN = '111-22-3333'"
> > > Set RS = conn.execute(SQLstmt)
> > >
> > > SQLstmt = "DELETE * FROM fd_2 WHERE CustomerSSN = '111-22-3333'"
> > > Set RS = conn.execute(SQLstmt)
> > >
> > > SQLstmt = "DELETE * FROM fd_3 WHERE CustomerSSN = '111-22-3333'"
> > > Set RS = conn.execute(SQLstmt)
> > >
> > > etc.....
> > >
> > >
> > > for some reason
> > >
> > > SQLstmt = "DELETE * FROM fd_1, fd_2, fd_3 WHERE CustomerSSN =
> > > '111-22-3333'"
> > > Set RS = conn.execute(SQLstmt)
> > >
> > > does not work.
> > >
> > > Mike
> > >
> > >
> >
>
Mark Schupp Guest
-
Bob Barrows #6 Re: How do you delete a record form multiple tables
Mike wrote:
No, no, no. that's not normal. Have one table with one debt column and one> In a answer to your last question.
>>>> Why does your database have all these tables like this?
> We have an Intranet that has an ASP driven site. On that site we
> input information about our customers.
> We do debt elimination and collect the debts of our clients and
> record them to a dbase. We have the ability
> to collect up to 20 different debts, such as credit cards, dept.
> store cards, etc... Not all of our clients have
> the same number of debts. I origionally had a big table that had 6
> fields about each debt and there were 20
> debts. That resulted in a table that was 120 columns. And if a
> client only had 5 debts then i was just waisting
> space by having the other 15 debts be null. So normalized the
> table and broke it into 20 seperate tables, one for
> each debt, that only have 6 records in each. Now I only record the
> information into a table if the information exists. So if a
> client only has 5 debts, i only write to tables 1-5.
debt-type column.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Bob Barrows Guest
-
McKirahan #7 Re: How do you delete a record form multiple tables
"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:evMk91PxDHA.2568@TK2MSFTNGP09.phx.gbl...Normalize! (as Bob strongly suggests!)> Mike wrote:>> > In a answer to your last question.
> >> >> >> Why does your database have all these tables like this?
> > We have an Intranet that has an ASP driven site. On that site we
> > input information about our customers.
> > We do debt elimination and collect the debts of our clients and
> > record them to a dbase. We have the ability
> > to collect up to 20 different debts, such as credit cards, dept.
> > store cards, etc... Not all of our clients have
> > the same number of debts. I origionally had a big table that had 6
> > fields about each debt and there were 20
> > debts. That resulted in a table that was 120 columns. And if a
> > client only had 5 debts then i was just waisting
> > space by having the other 15 debts be null. So normalized the
> > table and broke it into 20 seperate tables, one for
> > each debt, that only have 6 records in each. Now I only record the
> > information into a table if the information exists. So if a
> > client only has 5 debts, i only write to tables 1-5.
> No, no, no. that's not normal. Have one table with one debt column and one
> debt-type column.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
From an earlier post, I gather you have the following fields in 20 tables:
RD_# (where "#" is 1 - 20)
CustomerSSN
RDName
RDAccount
RDCurrentBalance
RDMinPayment
RDActualPayment
RDInterest
RDComments
Might I suggest these 2 tables instead?
Customer Table
CustomerSSN
RDName
Account Table
CustomerSSN
RDAccount
RDCurrentBalance
RDMinPayment
RDActualPayment
RDInterest
RDComments
Thus, the common key is "CustomerSSN" and there are one or more entries in
the Account Table.
McKirahan Guest
-
Rob Galante #8 Re: How do you delete a record form multiple tables
I never perform multiple operations that must maintain database
consistency and integrity inside an ASP script. I used a stored
procedure in SQL Server, and encompass all of the statements inside a
transaction. I use Update Queries if I'm using Access. I setup relations
between my table to enforce the constraints.
Inside the stored procedure, I check @@ERROR for != 0 after every SQL
statement, and jump to an error handler that returns the error code and
my own program-specific status code. Inside the error handler, I
rollback the transaction. Then I return a recordset with the status code
and error code.
If no error occurs, I commit the transaction. Then I return a recordset
with 0 for the status code, which signifies no error occurred.
Note that all my stored procedures return a recordset.
Rob
*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!
Rob Galante Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
