Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

delete records from table2 that are not in table1 Expand / Collapse
Author
Message
Posted Thursday, March 17, 2011 1:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 12:31 PM
Points: 1,271, Visits: 1,870
Hello, I need to delete records from table2 that are not in table1
I have a table1 and table2

table1

DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date
FROM table1

table2

DB, CODE, RATE, Date
FROM
table2

I need to delete records from table2 that are not in table1

Thank you
Post #1080001
Posted Thursday, March 17, 2011 2:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 5,472, Visits: 23,550
To properly assist you with tested T-SQL please post table definition(s), sample data and desired results.

To do so please click on the first link in my signature block for a method (including T-SQL statements) to assist us to assist you.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1080035
Posted Thursday, March 17, 2011 2:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 5,986, Visits: 6,932
Krasavita (3/17/2011)
Hello, I need to delete records from table2 that are not in table1
I have a table1 and table2

table1

DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date
FROM table1

table2

DB, CODE, RATE, Date
FROM
table2

I need to delete records from table2 that are not in table1

Thank you


As Ron mentioned, this is easier with actual DDL and sample data. But, for a quick question, a quick answer to get you on track. You want what's known as an anti-semi join. Left Join where null for the rest of us hacks.

Left join the table you're looking to delete from to the table that has the reference, and anytime the reference is missing (null), delete that row.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1080044
Posted Thursday, March 17, 2011 10:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 36,000, Visits: 30,293
Krasavita (3/17/2011)
Hello, I need to delete records from table2 that are not in table1
I have a table1 and table2

table1

DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date
FROM table1

table2

DB, CODE, RATE, Date
FROM
table2

I need to delete records from table2 that are not in table1

Thank you


EXCEPT will do a fine job on this. Have a look at it in Books Online.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1080142
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse