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.tablename is really Titledata)

Dim rstry As New ADODB.Recordset
Dim values As Variant
SQLQuery = "Select Numid from " & frmLogon.Tablename
Set rstry = frmLogon.cnConnection.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.cnConnection.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 ''," & _
"GeneralNote TEXT DEFAULT ''," & _
"Author VARCHAR(100) DEFAULT ''," & _
"Imprint VARCHAR(100) DEFAULT ''," & _
"ISBN VARCHAR(100) DEFAULT ''," & _
"Description VARCHAR(100) DEFAULT ''," & _
"CallNumberPre VARCHAR(5) DEFAULT ''," & _
"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"
& _
"Accession VARCHAR(25) DEFAULT ''," & _
"Bibliography VARCHAR(100) DEFAULT ''," & _
"Series VARCHAR(100) DEFAULT ''," & _
"MyStatus VARCHAR(70) DEFAULT ''," & _
"Barcode VARCHAR(50) DEFAULT ''," & _
"LocalData VARCHAR(100) DEFAULT ''," & _
"CheckoutPeriod VARCHAR(10) DEFAULT ''," & _
"CatalogCard 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 '')"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/

<sdowney717@.msn.com> wrote in message
news:1106400456.330578.120350@.z14g2000cwz.googlegr oups.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.tablename is really Titledata)
> Dim rstry As New ADODB.Recordset
> Dim values As Variant
> SQLQuery = "Select Numid from " & frmLogon.Tablename
> Set rstry = frmLogon.cnConnection.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.cnConnection.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 ''," & _
> "GeneralNote TEXT DEFAULT ''," & _
> "Author VARCHAR(100) DEFAULT ''," & _
> "Imprint VARCHAR(100) DEFAULT ''," & _
> "ISBN VARCHAR(100) DEFAULT ''," & _
> "Description VARCHAR(100) DEFAULT ''," & _
> "CallNumberPre VARCHAR(5) DEFAULT ''," & _
> "CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"
> & _
> "Accession VARCHAR(25) DEFAULT ''," & _
> "Bibliography VARCHAR(100) DEFAULT ''," & _
> "Series VARCHAR(100) DEFAULT ''," & _
> "MyStatus VARCHAR(70) DEFAULT ''," & _
> "Barcode VARCHAR(50) DEFAULT ''," & _
> "LocalData VARCHAR(100) DEFAULT ''," & _
> "CheckoutPeriod VARCHAR(10) DEFAULT ''," & _
> "CatalogCard 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 '')"|||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|||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/d...tu-sus_6btz.asp

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

<sdowney717@.msn.com> wrote in message
news:1106407049.791719.177000@.c13g2000cwb.googlegr oups.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|||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('Checked out by ',marcdata) <> '0' "

Dim rsTry As New ADODB.Recordset
rsTry.CursorLocation = adUseClient
frmLogon.cnConnection.Open
rsTry.Open SQLQuery, frmLogon.cnConnection, adOpenForwardOnly,
adLockOptimistic

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!MarcData, a - 1) &
Mid(rsTry!MarcData, b)
If b = 0 Then rsTry!MarcData = Left(rsTry!MarcData, a - 1) &
vbCrLf

rsTry.Update
End If
rsTry.MoveNext
Loop|||This will do a similar job in SQL (char(13)+char(10) replicate the &vbCrLf
in your code):

UPDATE BookData SET marcdata=left(marcdata, charindex('=904',marcdata)-1)+
CASE WHEN charindex('=', marcdata, charindex('=904', marcdata)+1)>0
THEN substring(marcdata, charindex('=', marcdata,
charindex('=904',marcdata)+1), 8000)
ELSE char(13)+char(10) 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(@.Start, @.Data)
IF @.Index>0
SET @.Data = Left(@.Data, @.Index-1)+
CASE WHEN charindex(@.Finish, @.Data, @.Index+1)>0
THEN substring(@.Data, charindex(@.Finish, @.Data, @.Index+1), 8000)
ELSE @.Append END
RETURN @.Data
END

GO

UPDATE BookData SET marcdata=dbo.STRIP(marcdata, '=904', '=',
char(13)+char(10)) 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=replace(marcdata,'=904','') 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

<sdowney717@.msn.com> wrote in message
news:1106408939.199966.309140@.f14g2000cwb.googlegr oups.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('Checked out by ',marcdata) <> '0' "
> Dim rsTry As New ADODB.Recordset
> rsTry.CursorLocation = adUseClient
> frmLogon.cnConnection.Open
> rsTry.Open SQLQuery, frmLogon.cnConnection, adOpenForwardOnly,
> adLockOptimistic
> 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!MarcData, a - 1) &
> Mid(rsTry!MarcData, b)
> If b = 0 Then rsTry!MarcData = Left(rsTry!MarcData, a - 1) &
> vbCrLf
> rsTry.Update
> End If
> rsTry.MoveNext
> Loop|||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(marcdata,
charindex('=904',marcdata)-1)+" & _
"CASE WHEN charindex('=', marcdata, charindex('=904',
marcdata)+1)>0" & _
"THEN substring(marcdata, charindex('=', marcdata,
charindex('=904',marcdata)+1), 8000)" & _
"ELSE char(13)+char(10) 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.|||heh, LEFT wont work on text, youll have to do a substring instead:

UPDATE BookData SET marcdata=substring(marcdata, 1,
charindex('=904',marcdata)-1)+
CASE WHEN charindex('=', marcdata, charindex('=904', marcdata)+1)>0
THEN substring(marcdata, charindex('=', marcdata,
charindex('=904',marcdata)+1), 8000)
ELSE char(13)+char(10) 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

<sdowney717@.msn.com> wrote in message
news:1106423250.001504.27280@.c13g2000cwb.googlegro ups.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(marcdata,
> charindex('=904',marcdata)-1)+" & _
> "CASE WHEN charindex('=', marcdata, charindex('=904',
> marcdata)+1)>0" & _
> "THEN substring(marcdata, charindex('=', marcdata,
> charindex('=904',marcdata)+1), 8000)" & _
> "ELSE char(13)+char(10) 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.|||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.|||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(10)
part of
the statement?|||I got it,
You leave out 'ELSE chr(10)+Chr(13)'. The END ends the case part of the
query.

It is working and even I can comprehend it
Thanks very much for all the help.|||hang on a sec, anything concat NULL yields NULL.

you will need ELSE '' END

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

<sdowney717@.msn.com> wrote in message
news:1106429668.551240.225390@.f14g2000cwb.googlegr oups.com...
>I got it,
> You leave out 'ELSE chr(10)+Chr(13)'. The END ends the case part of the
> query.
>
> It is working and even I can comprehend it
> Thanks very much for all the help.|||Yes, I just found out, it nulls it to nothing|||Ok, I put it in as '' and its is working.
Thanks for taking the time to look at this.|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> Is a text column size ever larger than 8000 bytes?

That's the whole point with using text. A single text value can accomdate
up to 2 GB of data.

> The data in this column is nowhere near that large and will at most be
> 4000 bytes or less.

In such case, I would considering to change the data type of the column to
varchar(4000), since text is quite cumbersome to handle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp