I have 2 tables, A and B. A contains 100 records, B contains a subset of 80 of those records. Both tables use ID as an index.
I want to create 20 new records in B for the records that are missing when compared with A.
Is it possible to do this in SQL rather than use a programming language? I know MySQL has a number of looping constructs, but I don’t know whether they can be used in this kind of scenario.
I managed to work this one out:INSERT INTO B (ID,Date)
SELECT A.ID,'1970-01-01 00:00'
FROM A LEFT JOIN B
ON A.ID=B.ID
WHERE B.ID IS NULLBy using a LEFT JOIN, all records in A are returned with all matching records in B. When a matching B record is not found, the B fields are set to NULL. By including a search for NULL in the WHERE clause, the SELECT statements returns all the IDs in A which don’t have a match in B.
In this case I’m also setting another field in B to a constant value for each new record. The fields to be updated in B are specified inside the brackets in the first line.