Skip to content

Instantly share code, notes, and snippets.

@mattm

mattm/solution.sql

Last active Jan 3, 2018
Embed
What would you like to do?
Identifying Missing Primary Keys
SET @mpy_id = (SELECT MAX(mpy_id) + 1 from members_payments);
DROP TABLE IF EXISTS members_payments_all_ids;
CREATE TEMPORARY TABLE members_payments_all_ids AS (
SELECT @mpy_id := @mpy_id - 1 AS mpy_id
FROM members_payments mp1, members_payments mp2
WHERE @mpy_id > 1
);
SELECT members_payments_all_ids.mpy_id
FROM members_payments_all_ids
LEFT JOIN members_payments ON members_payments_all_ids.mpy_id = members_payments.mpy_id
WHERE members_payments.mpy_id IS NULL
ORDER BY members_payments_all_ids.mpy_id ASC
+--------+
| mpy_id |
+--------+
| 2 |
| 3 |
| 5 |
| 7 |
| 8 |
| ... |
+--------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment