使用另一个表中的行从一个表中删除行 [英] Delete rows from one table using rows from another table

查看：71
本文介绍了使用另一个表中的行从一个表中删除行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

使用TitleData的NumId，我想使用纯SQL删除Bookdata中的

对应行。我希望它删除bookdata中所有

行，其中Titledata.NumID与bookdata.id匹配

这两个表的链接是因为表Titledata的NumId是

与表bookdata的Id相同。我可以，使用ADO，循环通过

逐个删除，但我想在纯SQL

语句中执行此操作。这可能吗？感谢任何帮助。


我在想这样的事情：


从Bookdata中删除Titledata.NumID = Bookdata.id ;

但当然会出错。


我目前的代码是：

（frmlogon.tablename真的是Titledata）


Dim rstry As New ADODB.Recordset

Dim values As Variant

SQLQuery =" Select Numid from" &安培; frmLogon.Tablename

设置rstry = frmLogon.cnConnection.Execute（SQLQuery）

values = rstry.GetRows

设置rstry = Nothing

''现在循环通过

Dim xx作为整数

xx = 0

Do xx> UBound（值，2）

SQLQuery ="从Bookdata中删除bookdata.Id =''" &安培;值（0，

xx）& "''"

frmLogon.cnConnection.Execute（SQLQuery）

xx = xx + 1

循环

''涉及2个表的create语句是


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''''，&& ; _

"描述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''''，" & amp; _

" 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

十进制（14,6）DEFAULT 0，" &安培; _

" Edits Char（1）DEFAULT''''，TitleDuplicate VARCHAR（50）DEFAULT''''）"

解决方案

DELETE BookData

WHERE EXISTS（SELECT * FROM TitleData WHERE TitleData.NumID = BookData.ID）


- 或者


DELETE BookData

来自BookData

JOIN TitleData ON TitleData.NumID = BookData.ID

>
方法（1）更好，因为服务器不必从连接中消除重复的

结果（对于给定的BookData可能有2个TitleData）

茶先生
http：//mr-tea.blogspot。 com /

< sd ******** @ msn.com>在消息中写道

news：11 ********************** @ z14g2000cwz.googlegr oups.com ...

使用TitleData中的NumId，我想使用纯SQL删除Bookdata中的
对应行。我希望它删除bookdata中的所有
行，其中Titledata.NumID与bookdata.id匹配
这两个表的链接是因为表Titledata的NumId与Id相同表bookdata。我可以，使用ADO，循环删除逐个删除，但我想在纯SQL语句中执行此操作。这可能吗？感谢任何帮助。

我在想这样的事情：

从Bookdata中删除Titledata.NumID = Bookdata.id

但当然会出错。

我目前的代码是：
（frmlogon.tablename真的是Titledata）

Dim rstry As New ADODB.Recordset
Dim values As Variant
SQLQuery =" Select Numid from &安培; frmLogon.Tablename
设置rstry = frmLogon.cnConnection.Execute（SQLQuery）
values = rstry.GetRows
设置rstry = Nothing
''现在循环通过
Dim xx作为整数
xx = 0
直到xx> UBound（值，2）
SQLQuery ="从Bookdata中删除bookdata.Id =''" &安培;值（0，
xx）& "''"
frmLogon.cnConnection.Execute（SQLQuery）
xx = xx + 1
循环

''参与的2个表的创建语句是

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''''，"& _
"说明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''''，"& _
条形码VARCHAR（50）DEFAULT''''，'&am p; _
LocalData VARCHAR（100）DEFAULT''''，"& _
" CheckoutPeriod VARCHAR（10）DEFAULT''''，" &安培; _
CatalogCard TEXT DEFAULT''''， &安培; _
摘要文字默认''''， &安培; _
MyCount VARCHAR（10）DEFAULT''''，" &安培; _
ItemDate DATETIME DEFAULT''''，" &安培; _
" MyUser VARCHAR（50）DEFAULT''''，" &安培; _
MarcData TEXT DEFAULT''''，" &安培; _
SdlsRecord TEXT DEFAULT''''，LOSC VARCHAR（5）DEFAULT''''，LOSN
十进制（14,6）DEFAULT 0，" &安培; _
" Edits Char（1）DEFAULT''''，TitleDuplicate VARCHAR（50）DEFAULT''''）"



非常感谢。

第一个工作正常

第二个适用于MySQL以及SQLServer


可以做任何事情完成使用ADO记录集的动作类型执行

使用纯SQL语句，包括在

字符串操作类型的查询中使用say CHARINDEX？比如你要在字符串中查找

然后在字符串中添加文本或删除文本

特定位置？


我


在字符串超过8000字节之前，MS-TSQL可能还不错，

然后它会变得混乱。

SQL2K上的UDF可用于封装可重用的字符串操作代码。


例如
http://msdn.microsoft .com / library / de ... u-sus_6btz.asp


茶先生
http://mr-tea.blogspot.com


< sd ****** **@msn.com>在消息中写道

news：11 ********************** @ c13g2000cwb.googlegr oups.com ...

非常感谢。
第一个工作正常
第二个适用于MySQL以及SQLServer

使用ADO记录集类型的动作可以完成任何事情使用纯SQL语句执行
使用在字符串操作类型的查询中使用CHARINDEX吗？比如你在字符串中查找某些内容然后在字符串中的特定位置添加文本或删除文本？

我



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/
<sd********@msn.com> wrote in message
news:11**********************@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/de...u-sus_6btz.asp

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

<sd********@msn.com> wrote in message
news:11**********************@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



这篇关于使用另一个表中的行从一个表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆