Delete based on a subquery
Question
In our previous exercises, we deleted a specific member who had never made a booking. How can we make that more general, to delete all members who have never made a booking?
Expected Results
| memid | surname | firstname | address | zipcode | telephone | recommendedby | joindate |
|---|---|---|---|---|---|---|---|
| 0 | GUEST | GUEST | GUEST | 0 | (000) 000-0000 | NULL | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) |
| 1 | Smith | Darren | 8 Bloomsbury Close, Boston | 4321 | 555-555-5555 | NULL | Mon Jul 02 2012 12:02:05 GMT+0000 (Coordinated Universal Time) |
| 2 | Smith | Tracy | 8 Bloomsbury Close, New York | 4321 | 555-555-5555 | NULL | Mon Jul 02 2012 12:08:23 GMT+0000 (Coordinated Universal Time) |
| 3 | Rownam | Tim | 23 Highway Way, Boston | 23423 | (844) 693-0723 | NULL | Tue Jul 03 2012 09:32:15 GMT+0000 (Coordinated Universal Time) |
| 4 | Joplette | Janice | 20 Crossing Road, New York | 234 | (833) 942-4710 | 1 | Tue Jul 03 2012 10:25:05 GMT+0000 (Coordinated Universal Time) |
| 5 | Butters | Gerald | 1065 Huntingdon Avenue, Boston | 56754 | (844) 078-4130 | 1 | Mon Jul 09 2012 10:44:09 GMT+0000 (Coordinated Universal Time) |
| 6 | Tracy | Burton | 3 Tunisia Drive, Boston | 45678 | (822) 354-9973 | NULL | Sun Jul 15 2012 08:52:55 GMT+0000 (Coordinated Universal Time) |
| 7 | Dare | Nancy | 6 Hunting Lodge Way, Boston | 10383 | (833) 776-4001 | 4 | Wed Jul 25 2012 08:59:12 GMT+0000 (Coordinated Universal Time) |
| 8 | Boothe | Tim | 3 Bloomsbury Close, Reading, 00234 | 234 | (811) 433-2547 | 3 | Wed Jul 25 2012 16:02:35 GMT+0000 (Coordinated Universal Time) |
| 9 | Stibbons | Ponder | 5 Dragons Way, Winchester | 87630 | (833) 160-3900 | 6 | Wed Jul 25 2012 17:09:05 GMT+0000 (Coordinated Universal Time) |
| 10 | Owen | Charles | 52 Cheshire Grove, Winchester, 28563 | 28563 | (855) 542-5251 | 1 | Fri Aug 03 2012 19:42:37 GMT+0000 (Coordinated Universal Time) |
| 11 | Jones | David | 976 Gnats Close, Reading | 33862 | (844) 536-8036 | 4 | Mon Aug 06 2012 16:32:55 GMT+0000 (Coordinated Universal Time) |
| 12 | Baker | Anne | 55 Powdery Street, Boston | 80743 | 844-076-5141 | 9 | Fri Aug 10 2012 14:23:22 GMT+0000 (Coordinated Universal Time) |
| 13 | Farrell | Jemima | 103 Firth Avenue, North Reading | 57392 | (855) 016-0163 | NULL | Fri Aug 10 2012 14:28:01 GMT+0000 (Coordinated Universal Time) |
| 14 | Smith | Jack | 252 Binkington Way, Boston | 69302 | (822) 163-3254 | 1 | Fri Aug 10 2012 16:22:05 GMT+0000 (Coordinated Universal Time) |
| 15 | Bader | Florence | 264 Ursula Drive, Westford | 84923 | (833) 499-3527 | 9 | Fri Aug 10 2012 17:52:03 GMT+0000 (Coordinated Universal Time) |
| 16 | Baker | Timothy | 329 James Street, Reading | 58393 | 833-941-0824 | 13 | Wed Aug 15 2012 10:34:25 GMT+0000 (Coordinated Universal Time) |
| 17 | Pinker | David | 5 Impreza Road, Boston | 65332 | 811 409-6734 | 13 | Thu Aug 16 2012 11:32:47 GMT+0000 (Coordinated Universal Time) |
| 20 | Genting | Matthew | 4 Nunnington Place, Wingfield, Boston | 52365 | (811) 972-1377 | 5 | Sun Aug 19 2012 14:55:55 GMT+0000 (Coordinated Universal Time) |
| 21 | Mackenzie | Anna | 64 Perkington Lane, Reading | 64577 | (822) 661-2898 | 1 | Sun Aug 26 2012 09:32:05 GMT+0000 (Coordinated Universal Time) |
| 22 | Coplin | Joan | 85 Bard Street, Bloomington, Boston | 43533 | (822) 499-2232 | 16 | Wed Aug 29 2012 08:32:41 GMT+0000 (Coordinated Universal Time) |
| 24 | Sarwin | Ramnaresh | 12 Bullington Lane, Boston | 65464 | (822) 413-1470 | 15 | Sat Sep 01 2012 08:44:42 GMT+0000 (Coordinated Universal Time) |
| 26 | Jones | Douglas | 976 Gnats Close, Reading | 11986 | 844 536-8036 | 11 | Sun Sep 02 2012 18:43:05 GMT+0000 (Coordinated Universal Time) |
| 27 | Rumney | Henrietta | 3 Burkington Plaza, Boston | 78533 | (822) 989-8876 | 20 | Wed Sep 05 2012 08:42:35 GMT+0000 (Coordinated Universal Time) |
| 28 | Farrell | David | 437 Granite Farm Road, Westford | 43532 | (855) 755-9876 | NULL | Sat Sep 15 2012 08:22:05 GMT+0000 (Coordinated Universal Time) |
| 29 | Worthington-Smyth | Henry | 55 Jagbi Way, North Reading | 97676 | (855) 894-3758 | 2 | Mon Sep 17 2012 12:27:15 GMT+0000 (Coordinated Universal Time) |
| 30 | Purview | Millicent | 641 Drudgery Close, Burnington, Boston | 34232 | (855) 941-9786 | 2 | Tue Sep 18 2012 19:04:01 GMT+0000 (Coordinated Universal Time) |
| 33 | Tupperware | Hyacinth | 33 Cheerful Plaza, Drake Road, Westford | 68666 | (822) 665-5327 | NULL | Tue Sep 18 2012 19:32:05 GMT+0000 (Coordinated Universal Time) |
| 35 | Hunt | John | 5 Bullington Lane, Boston | 54333 | (899) 720-6978 | 30 | Wed Sep 19 2012 11:32:45 GMT+0000 (Coordinated Universal Time) |
| 36 | Crumpet | Erica | Crimson Road, North Reading | 75655 | (811) 732-4816 | 2 | Sat Sep 22 2012 08:36:38 GMT+0000 (Coordinated Universal Time) |
Your Answer
Your Results
Loading database...
Answers and Discussion
delete from cd.members where memid not in (select memid from cd.bookings);We can use subqueries to determine whether a row should be deleted or not. There's a couple of standard ways to do this. In our featured answer, the subquery produces a list of all the different member ids in the cd.bookings table. If a row in the table isn't in the list generated by the subquery, it gets deleted.
An alternative is to use a correlated subquery. Where our previous example runs a large subquery once, the correlated approach instead specifies a smaller subqueryto run against every row.
delete from cd.members mems where not exists (select 1 from cd.bookings where memid = mems.memid);The two different forms can have different performance characteristics. Under the hood, your database engine is free to transform your query to execute it in a correlated or uncorrelated fashion, though, so things can be a little hard to predict.