473,888 Members | 2,172 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Delete rows from one table using rows from another table

Using the the NumId from TitleData, I would like to delete the
corresponding row in Bookdata using pure SQL. I want it to delete all
rows in bookdata where the Titledata.NumID is a match to bookdata.id
The two tables are linked in that the NumId of table Titledata is
identical to the Id of table bookdata. I can, using ADO, loop thru
deleting one by one but I would like to do this in a pure SQL
statement. Is this possible? Any help is appreciated.

I was thinking something like this way :

"Delete from Bookdata where Titledata.NumID = Bookdata.id"
But of course it will error.

My current code is:
(frmlogon.table name is really Titledata)

Dim rstry As New ADODB.Recordset
Dim values As Variant
SQLQuery = "Select Numid from " & frmLogon.Tablen ame
Set rstry = frmLogon.cnConn ection.Execute( SQLQuery)
values = rstry.GetRows
Set rstry = Nothing
'now loop thru
Dim xx As Integer
xx = 0
Do Until xx > UBound(values, 2)
SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,
xx) & "'"
frmLogon.cnConn ection.Execute (SQLQuery)
xx = xx + 1
Loop
'create statements for 2 tables involved are

conn.Execute "CREATE TABLE TitleData" & _
"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _
"NumId INT DEFAULT 0 )"

conn.Execute "CREATE TABLE BookData" & _
"(Id INT IDENTITY (1, 1) NOT NULL," & _
"Titles TEXT DEFAULT ''," & _
"GeneralNot e TEXT DEFAULT ''," & _
"Author VARCHAR(100) DEFAULT ''," & _
"Imprint VARCHAR(100) DEFAULT ''," & _
"ISBN VARCHAR(100) DEFAULT ''," & _
"Descriptio n VARCHAR(100) DEFAULT ''," & _
"CallNumber Pre VARCHAR(5) DEFAULT ''," & _
"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"
& _
"Accession VARCHAR(25) DEFAULT ''," & _
"Bibliograp hy VARCHAR(100) DEFAULT ''," & _
"Series VARCHAR(100) DEFAULT ''," & _
"MyStatus VARCHAR(70) DEFAULT ''," & _
"Barcode VARCHAR(50) DEFAULT ''," & _
"LocalData VARCHAR(100) DEFAULT ''," & _
"CheckoutPe riod VARCHAR(10) DEFAULT ''," & _
"CatalogCar d TEXT DEFAULT ''," & _
"Summary TEXT DEFAULT ''," & _
"MyCount VARCHAR(10) DEFAULT ''," & _
"ItemDate DATETIME DEFAULT ''," & _
"MyUser VARCHAR(50) DEFAULT ''," & _
"MarcData TEXT DEFAULT ''," & _
"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSN
Decimal(14,6) DEFAULT 0," & _
"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"

Jul 23 '05 #1
14 16254
DELETE BookData
WHERE EXISTS(SELECT * FROM TitleData WHERE TitleData.NumID =BookData.ID)

-- OR

DELETE BookData
FROM BookData
JOIN TitleData ON TitleData.NumID =BookData.ID

Method (1) is better because the server will not have to eliminate duplicate
results from the join (there may be 2 TitleData for a given BookData)

Mr Tea
http://mr-tea.blogspot.com/
<sd********@msn .com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
Using the the NumId from TitleData, I would like to delete the
corresponding row in Bookdata using pure SQL. I want it to delete all
rows in bookdata where the Titledata.NumID is a match to bookdata.id
The two tables are linked in that the NumId of table Titledata is
identical to the Id of table bookdata. I can, using ADO, loop thru
deleting one by one but I would like to do this in a pure SQL
statement. Is this possible? Any help is appreciated.

I was thinking something like this way :

"Delete from Bookdata where Titledata.NumID = Bookdata.id"
But of course it will error.

My current code is:
(frmlogon.table name is really Titledata)

Dim rstry As New ADODB.Recordset
Dim values As Variant
SQLQuery = "Select Numid from " & frmLogon.Tablen ame
Set rstry = frmLogon.cnConn ection.Execute( SQLQuery)
values = rstry.GetRows
Set rstry = Nothing
'now loop thru
Dim xx As Integer
xx = 0
Do Until xx > UBound(values, 2)
SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,
xx) & "'"
frmLogon.cnConn ection.Execute (SQLQuery)
xx = xx + 1
Loop
'create statements for 2 tables involved are

conn.Execute "CREATE TABLE TitleData" & _
"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _
"NumId INT DEFAULT 0 )"

conn.Execute "CREATE TABLE BookData" & _
"(Id INT IDENTITY (1, 1) NOT NULL," & _
"Titles TEXT DEFAULT ''," & _
"GeneralNot e TEXT DEFAULT ''," & _
"Author VARCHAR(100) DEFAULT ''," & _
"Imprint VARCHAR(100) DEFAULT ''," & _
"ISBN VARCHAR(100) DEFAULT ''," & _
"Descriptio n VARCHAR(100) DEFAULT ''," & _
"CallNumber Pre VARCHAR(5) DEFAULT ''," & _
"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"
& _
"Accession VARCHAR(25) DEFAULT ''," & _
"Bibliograp hy VARCHAR(100) DEFAULT ''," & _
"Series VARCHAR(100) DEFAULT ''," & _
"MyStatus VARCHAR(70) DEFAULT ''," & _
"Barcode VARCHAR(50) DEFAULT ''," & _
"LocalData VARCHAR(100) DEFAULT ''," & _
"CheckoutPe riod VARCHAR(10) DEFAULT ''," & _
"CatalogCar d TEXT DEFAULT ''," & _
"Summary TEXT DEFAULT ''," & _
"MyCount VARCHAR(10) DEFAULT ''," & _
"ItemDate DATETIME DEFAULT ''," & _
"MyUser VARCHAR(50) DEFAULT ''," & _
"MarcData TEXT DEFAULT ''," & _
"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSN
Decimal(14,6) DEFAULT 0," & _
"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"

Jul 23 '05 #2
Thank so very much.
The first one works fine
The second one works with MySQL as well as SQLServer

Can anything done using ADO recordset type of action be performed
using pure SQL statements including something using say CHARINDEX in a
string manipulation type of query? Such as where you would be looking
in the string for something and then add in text or delete text at
specific positions in the string?

I

Jul 23 '05 #3
Probably, MS-TSQL is pretty good until the strings get above 8000 bytes,
then it gets messy.
UDFs on SQL2K can be used to encapsulate re-usable string manipulation code.

e.g.
http://msdn.microsoft.com/library/de...u-sus_6btz.asp

Mr Tea
http://mr-tea.blogspot.com

<sd********@msn .com> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. .
Thank so very much.
The first one works fine
The second one works with MySQL as well as SQLServer

Can anything done using ADO recordset type of action be performed
using pure SQL statements including something using say CHARINDEX in a
string manipulation type of query? Such as where you would be looking
in the string for something and then add in text or delete text at
specific positions in the string?

I

Jul 23 '05 #4
Here is how I do it using ADO recordsets.
I look for 'Checked out by ' in the text field called marcdata
I then look in each row returned for '=904' and delete all text from
the marcdata string up to the next equal sign.
If another equal sign is not found then I just take out the whole part
of the string right to the end.

Can this be done using pure SQL?

SQLQuery = "Select Id, marcdata from Bookdata where
CHARINDEX('Chec ked out by ',marcdata) <> '0' "

Dim rsTry As New ADODB.Recordset
rsTry.CursorLoc ation = adUseClient
frmLogon.cnConn ection.Open
rsTry.Open SQLQuery, frmLogon.cnConn ection, adOpenForwardOn ly,
adLockOptimisti c

Do Until rsTry.EOF
'update local status
'remove it from marc if present
a = InStr(1, rsTry!MarcData, "=904")
If a <> 0 Then
b = InStr(a + 1, rsTry!MarcData, "=")
If b <> 0 Then rsTry!MarcData = Left(rsTry!Marc Data, a - 1) &
Mid(rsTry!MarcD ata, b)
If b = 0 Then rsTry!MarcData = Left(rsTry!Marc Data, a - 1) &
vbCrLf

rsTry.Update
End If
rsTry.MoveNext
Loop

Jul 23 '05 #5
This will do a similar job in SQL (char(13)+char( 10) replicate the &vbCrLf
in your code):

UPDATE BookData SET marcdata=left(m arcdata, charindex('=904 ',marcdata)-1)+
CASE WHEN charindex('=', marcdata, charindex('=904 ', marcdata)+1)>0
THEN substring(marcd ata, charindex('=', marcdata,
charindex('=904 ',marcdata)+1), 8000)
ELSE char(13)+char(1 0) END
WHERE marcdata LIKE '%Checked out by%' AND marcdata LIKE '%=904%'
you could turn this into a function that makes it more readable and allows
re-use (SQL2K):

CREATE FUNCTION dbo.Strip (@Data varchar(8000), @Start varchar(50), @Finish
varchar(50), @Append varchar(50))
RETURNS varchar(8000) AS
BEGIN
DECLARE @Index int
SET @Index = CharIndex(@Star t, @Data)
IF @Index>0
SET @Data = Left(@Data, @Index-1)+
CASE WHEN charindex(@Fini sh, @Data, @Index+1)>0
THEN substring(@Data , charindex(@Fini sh, @Data, @Index+1), 8000)
ELSE @Append END
RETURN @Data
END

GO

UPDATE BookData SET marcdata=dbo.ST RIP(marcdata, '=904', '=',
char(13)+char(1 0)) WHERE marcdata LIKE '%Checked out by%' AND marcdata LIKE
'%=904%'
if you are just looking to remove an '=904' from the string then it becomes
much simpler:

UPDATE BookData SET markdata=replac e(marcdata,'=90 4','') LIKE '%Checked out
by%' AND marcdata LIKE '%=904%'
Another way maybe to normalise that data into a checkout table and avoid the
need for string manipulation completely.

CREATE TABLE dbo.Checkout
(
NumID int,
data int
)
GO
DELETE Checkout WHERE data=904

Mr Tea
http://mr-tea.blogspot.com

<sd********@msn .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .
Here is how I do it using ADO recordsets.
I look for 'Checked out by ' in the text field called marcdata
I then look in each row returned for '=904' and delete all text from
the marcdata string up to the next equal sign.
If another equal sign is not found then I just take out the whole part
of the string right to the end.

Can this be done using pure SQL?

SQLQuery = "Select Id, marcdata from Bookdata where
CHARINDEX('Chec ked out by ',marcdata) <> '0' "

Dim rsTry As New ADODB.Recordset
rsTry.CursorLoc ation = adUseClient
frmLogon.cnConn ection.Open
rsTry.Open SQLQuery, frmLogon.cnConn ection, adOpenForwardOn ly,
adLockOptimisti c

Do Until rsTry.EOF
'update local status
'remove it from marc if present
a = InStr(1, rsTry!MarcData, "=904")
If a <> 0 Then
b = InStr(a + 1, rsTry!MarcData, "=")
If b <> 0 Then rsTry!MarcData = Left(rsTry!Marc Data, a - 1) &
Mid(rsTry!MarcD ata, b)
If b = 0 Then rsTry!MarcData = Left(rsTry!Marc Data, a - 1) &
vbCrLf

rsTry.Update
End If
rsTry.MoveNext
Loop

Jul 23 '05 #6
I am getting an error which reads out as:
Argument data type text is invalid for argument 1 of left function.

The marcdata column is a text column

I have to put this in a string to execute it like this:

SQLQuery = "UPDATE BookData SET marcdata=left(m arcdata,
charindex('=904 ',marcdata)-1)+" & _
"CASE WHEN charindex('=', marcdata, charindex('=904 ',
marcdata)+1)>0" & _
"THEN substring(marcd ata, charindex('=', marcdata,
charindex('=904 ',marcdata)+1), 8000)" & _
"ELSE char(13)+char(1 0) END WHERE marcdata LIKE '%Checked
out by%' AND marcdata LIKE '%=904%'"

The checkout data is stored in its own table, this is mostly for backup
and or viewing
if someone happens to browse thru the marc field tag
information that is stored for each item.

Jul 23 '05 #7
heh, LEFT wont work on text, youll have to do a substring instead:

UPDATE BookData SET marcdata=substr ing(marcdata, 1,
charindex('=904 ',marcdata)-1)+
CASE WHEN charindex('=', marcdata, charindex('=904 ', marcdata)+1)>0
THEN substring(marcd ata, charindex('=', marcdata,
charindex('=904 ',marcdata)+1), 8000)
ELSE char(13)+char(1 0) END
WHERE marcdata LIKE '%Checked out by%' AND marcdata LIKE '%=904%'

Keep in mind that SQL has problems with data much longer than 8000 bytes.

Mr Tea
http://mr-tea.blogspot.com

<sd********@msn .com> wrote in message
news:11******** *************@c 13g2000cwb.goog legroups.com...
I am getting an error which reads out as:
Argument data type text is invalid for argument 1 of left function.

The marcdata column is a text column

I have to put this in a string to execute it like this:

SQLQuery = "UPDATE BookData SET marcdata=left(m arcdata,
charindex('=904 ',marcdata)-1)+" & _
"CASE WHEN charindex('=', marcdata, charindex('=904 ',
marcdata)+1)>0" & _
"THEN substring(marcd ata, charindex('=', marcdata,
charindex('=904 ',marcdata)+1), 8000)" & _
"ELSE char(13)+char(1 0) END WHERE marcdata LIKE '%Checked
out by%' AND marcdata LIKE '%=904%'"

The checkout data is stored in its own table, this is mostly for backup
and or viewing
if someone happens to browse thru the marc field tag
information that is stored for each item.

Jul 23 '05 #8
That works very well.
Is a text column size ever larger than 8000 bytes?
The data in this column is nowhere near that large and will at most be
4000 bytes or less.

Jul 23 '05 #9
I found out I dont need the VbCrLf added into the string after all
How would this line be coded leaving out the ELSE char(13)+char(1 0)
part of
the statement?

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
11361
by: Gert Schumann | last post by:
I want to delete just parts of tables, so I can't use 'TRUNCATE'. As I want to delete about millions of lines, I need a very big rollback segment. The best way would be to delete without using rollback because the delete really takes a lot of time copying all data into the rollback file. Is this possible with oracle? Many thanks for any help! Gert
7
2988
by: Philip Mette | last post by:
I have been searching many postings and I cant seem to find anyone that has this answer so I decided to post. I am using SQL (Transact-SQL).If I have 2 tables with columns acct_num,activity_date,and pay_amt and I want to delete one instance of a record in table 1 for every instance of that record in table 2 how could I do that. For example. Table 1 ----------- acct activity_date pay_amt
16
3887
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the table, or perhaps bytes, being updated where the engine just decides, screw it, i'll just make a new one. surfed this group and google, but couldn't find anything. the context: we have some java folk who like to parametize/
2
8614
by: Zarrin | last post by:
Hello, I read several articles of newsgroup about the bulk delete, and I found one way is to: -create a temporary table with all constraints of original table -insert rows to be retained into that temp table -drop constraints on original table -drop the original table -rename the temporary table
5
9944
by: Neil | last post by:
I am getting time-out errors when I try to perform a simple delete on a linked server. The command is: Delete From MyTable Where PKID=12345 I have tried executing this command directly from Query Analyzer, but it just times out. However, when I run it from QA against the server itself (rather than from my local server against a linked server), it executes immediately. Similarly, if I run the same SQL command through an ODBC linked...
6
3870
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called "Generations"), and it allows the user to add, edit and delete the various records of the table. "Generations" table has the following fields: "IDPerson", NamePerson", "AgePerson" and "IDParent". A record contains the information about a person (his name, his...
5
3116
by: rn5a | last post by:
The .NET 2.0 documentation states the following: When using a DataSet or DataTable in conjunction with a DataAdapter & a relational data source, use the Delete method of the DataRow to remove the row. The Delete method marks the row as Deleted in the DataSet or DataTable but does not remove it. Instead when the DataAdapter encounters a row marked as Deleted, it executes its DeleteCommand method to delete the row at the data source. The...
3
12354
by: Michel Esber | last post by:
Hello, Environment: DB2 LUW v8 FP15 / Linux I have a table with 50+ Million rows. The table structure is basically (ID - Timestamp). I have two main applications - one inserting rows, and the other reading/deleting rows. The 'deleter' application runs a MIN/MAX (timestamp) for each ID and, if the difference between min/max is greater than 1h, it reads all
29
5355
by: shivasusan | last post by:
Hi! I can add rows with inputs to my HTML table dynamically using DOM, but I cannot remove selected rows. In fact, every row contains a Delete button. So, user selects the rows to remove, clicks the delete button selected row wants to delete. My problem is(If i delete the first row, its say undefined and change the value is 0) pls check my program. <%pathdefiner = "../"%> <!--#include file="../connection/connector.asp" -->...
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10772
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10434
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9593
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7143
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5810
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4635
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4239
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.