1

I am trying to get my head around this delete statement I am trying to accomplish. I understand how to delete from table where column not in ( subquery to table2)

I have two tables with multiple columns, and each table has two columns making up the primary keys. I think you have to do a join maybe? I would like something in the lines of the following:

Delete From tbl1 left join tbl2
on tbl1.PK1 = tbl2.PK1 and tbl1.PK2 = tbl2.PK2
where tbl1_PK1 Not IN(
Select Distinct tbl2.PK1, tbl2.PK2 where DateStr >= GetDate()-365)

I am trying remove rows from tbl1. Does this look close to correct?

Edit to add:

tbl1 PK1, PK2, Col1

tbl2 PK1,PK2, DateStr

Delete From tbl1 left join tbl2
on tbl1.PK1 = tbl2.PK1 and tbl1.PK2 = tbl2.PK2
where tbl1.PK1,tbl1.PK2 Not IN(
Select Distinct tbl2.PK1, tbl2.PK2 where DateStr >= GetDate()-365)

What is left after running sqlfiddle should be:

tbl1
('T', '222','asdf')

tbl2
('T', '222','9/26/2014')
  • On the Where Clause, I am trying to make the comparison to tb1_pk1 and tbl1_pk2 at the same time. – eripey Aug 14 '14 at 18:59
  • SHOW CREATE tbl1 (abd tbl2). Provide some sample DML (INSERT INTO tbl1 (tbl2) VALUES(...)) and then give us the result you want with the logic you used to get it. You could put all of this on SQLFiddle if that suits you. What RDBMS are you using? – Vérace Aug 14 '14 at 19:05
  • I am using sql 2008. I will make a sqlfiddle. Thanks. – eripey Aug 14 '14 at 19:14
  • A little nitpicking: the DBMS you are using is called SQL Server 2008. "SQL 2008" is not a DBMS product - it's an ISO standard that defines the SQL query language – a_horse_with_no_name Aug 14 '14 at 19:20
  • 1
    Could you also put the result you want (i.e. how the table should look after this SQL) here or on SQLFiddle. It's very confusing having columns you call PK1/2 and that they're not PRIMARY KEYs. Call them col1, col2 and col3. Also, I assume that tbl2.col3 is what you refer to as Datestr in the query? – Vérace Aug 14 '14 at 19:56
5

I suspect you're after something like this:

DELETE FROM tbl1
FROM tbl1
    LEFT OUTER JOIN tbl2
        ON tbl1.PK1 = tbl2.PK1
        AND tbl1.PK2 = tbl2.PK2
        AND tbl2.DateStr >= GETDATE() - 365
WHERE tbl2.PK1 IS NULL
    AND tbl2.PK2 IS NULL

Yes, you have two FROM clauses with a multi-table DELETE statement. This is essentially just an anti-join turned into a deletion.

  • 1
    +1. No need for the 2 condition in the end. WHERE tbl2.PK1 IS NULL; is enough. – ypercubeᵀᴹ Aug 14 '14 at 20:42
  • What is the purpose of the 'WHERE tbl2.PK1 IS NULL' – eripey Aug 14 '14 at 21:47
  • Can you check out this sqlfiddle.com/#!3/d0b50/13 doesn't seem to be removing any rows from tbl1. Nevermind, looks like the command works! Thanks! – eripey Aug 14 '14 at 21:53
  • Now if I want to use the same rule for tbl2. This should work? DELETE FROM tbl2 where tbl2.DateStr >= GETDATE() - 366 Probably not this simple. since this has the opposite effect that I wanted LOL So I tried this DELETE FROM tbl2 where tbl2.DateStr <= GETDATE() - 366 and it works. – eripey Aug 14 '14 at 22:05
  • Thanks for the answer, I was able to figure it out with your help! I can't vote up or else I would. – eripey Aug 14 '14 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.