Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is my first time here, so please bear with me.

Assuming i have 2 table,

Table 1
Date | Closing
1/1/2013 | 1
2/1/2013 | 2

Table 2
Date | Closing
1/1/2013 | 3
3/1/2013 | 4

What sql statements allow me to keep rows such that only Date that exists in both table remains?

End Result:

Table 1
Date | Closing
1/1/2013 | 1

Table 2
Date | Closing
1/1/2013 | 3

share|improve this question
    
Do you want perform a DELETE action of rows 2/1/2013 of the first table and 3/1/2013 of the second table? –  Joe Taras Dec 18 '13 at 9:01
add comment

1 Answer

up vote 1 down vote accepted

If you want to perform a DELETE action:

You'll delete all rows in table1 not present in table2 (by date field) and viceversa

DELETE FROM table1
WHERE NOT EXISTS(
    SELECT 'X'
    FROM table2
    WHERE table1.date = table2.date
)

and

DELETE FROM table2
WHERE NOT EXISTS(
    SELECT 'X'
    FROM table1
    WHERE table2.date = table1.date
)

If you want to perform a SELECT action

You'll show only rows presents in table1 and table2

SELECT * FROM table1
WHERE EXISTS(
    SELECT 'X'
    FROM table2
    WHERE table1.date = table2.date
)
UNION ALL
SELECT * FROM table2
WHERE EXISTS(
    SELECT 'X'
    FROM table1
    WHERE table2.date = table1.date
)
share|improve this answer
    
Thanks! Both solutions are useful to me. –  adsisco Dec 18 '13 at 9:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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