473,320 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

delete records from one table that occur in another

hi,

i have tableA with column dvd_id and tableB with client_ID and dvd_id.

i want to remove from tableA all the records where tableB has the same dvd_id for a specific client_ID.

tableA tableB
dvd_id client_id dvd_id
17 1 12
12 1 15
13 1 27
14 2 12
15 3 15

ie: remove from tableA records with dvd_id of 12 and 15.

note: tableA is specific to client_id 1 thus removing all the dvd_ids that already are in table B...only reccommend dvd_id's not in tableB

i would imagine looping through tableB for a specific client_id settin a temp variable to = dvd_id for that record and then remove that id from tableA

maybe a better way?

Thanks for any help.
Feb 14 '07 #1
4 1919
awesome i worked it out! :)

used a cursor...didnt know about those or how to use them but got it!
Feb 14 '07 #2
almaz
168 Expert 100+
Cursors should be avoided whenever possible. As I understood, you have a specific value for client_id, and you want to delete records from tableA that have corresponding records in tableB for specified client. Than it can be done as easy as:
Expand|Select|Wrap|Line Numbers
  1. declare @client_id int
  2. set @client_id = 1
  3.  
  4. delete from tableA
  5. where dvd_id in (select dvd_id from tableB where client_id = @client_id)
Feb 14 '07 #3
JAGUK
1
awesome i worked it out! :)

used a cursor...didnt know about those or how to use them but got it!
Not sure cursor is best/simplest way.
Could use:
delete from TableA
where dvd_id in (select dvd_id from TableB)
Feb 14 '07 #4
wow that was easier...

didn't know about that 'in' function

I assume cursors are slower..

thanks for the help!
Feb 15 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

7
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...
5
by: Alex | last post by:
Hi, hi have a table with 2.5 million records which i try do delete. i have several constraints on it too. i tried to delete the records using delete but it does not seem to work. the delete runs...
8
by: paulwilliamsonremove | last post by:
Hi, Using Access 2003. I am trying to delete records from one table/query ("qryHistoryPersonIDs") if they don't exist in another table ("qryDonations"). But the SQL syntax I came up with is...
3
by: John Rivers | last post by:
Hello, I think this will apply to alot of web applications: users want the ability to delete a record in table x this record is related to records in other tables and those to others in...
6
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...
17
by: (PeteCresswell) | last post by:
I've got apps where you *really* wouldn't want to delete certain items by accident, but the users just have to have a "Delete" button. My current strategies: Plan A:...
1
by: shades234 | last post by:
I was wondering if you can run a delete query in one database, and have the action take place in another database. Namely, do something like Delete (fields) IN (Database location) FROM (specify...
5
by: Bob Bridges | last post by:
Start with two tables, parent records in one and child records in the other, a one-to-many relationship. Create a select statement joining the two. Display the query in datasheet mode. When I...
10
by: nickvans | last post by:
Hello everyone, I'm fairly new to VBA and MS Access (I'm using 2003) but my issue seems like a pretty straight forward one. I would like to delete all records found in one table from another one. ...
1
by: Butever | last post by:
There is a need to delete all record from one table that occur in another table. Can I get advise how to do that with SQL? Thanks. Example: Table A: Company_id App_id User_id 1 ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.