Ted wrote:Actually, you can't do that in any version of MySQL, because they have a> Given two tables, I need to insert each record in the first into the
> second that isn't already in the second,
> If I am not mistaken, the following statement, or something like it,
> will meet the first requirement.
> INSERT INTO tbl_2_name (id)
> SELECT id FROM tbl_1_name
> WHERE tbl_1_name.id NOT IN (SELECT id FROM tbl_2_name);
limitation that you can't INSERT into a table and SELECT from the same
table in a statement (including subqueries of that statement). Aside
from that limitation, your logic is correct.
You can accomplish this task with INSERT IGNORE. Assuming you have a
primary key on tbl_2_name, you could simply do this:
INSERT IGNORE INTO tbl_2_name (id) SELECT id FROM tbl_1_name;
Rows will be inserted to tbl_2_name only if the same value did not
already exist in that table. The other rows will generate warnings,
which you can safely ignore.
I'm not sure I understand what you mean here. Do you want to INSERT> I am not quite sure how best to structure the statement that will
> accomplish the second. ... I need to insert the
> CURRENT_TIMESTAMP in a specific field in the second if it is not
> in the first. ...
> INSERT INTO tbl_2_name (some_time)
> SELECT NOW()
> WHERE tbl_2_name.id NOT IN (SELECT id FROM tbl_1_name);
_new_ records in tbl_2_name with CURRENT_TIMESTAMP, or do you want to
UPDATE existing records in tbl_2_name where they do not exist in
tbl_1_name? It seems like you are trying to do the latter.
I would use a multi-table UPDATE with an outer join:
UPDATE tbl_2_name t2 LEFT JOIN tbl_1_name t1 ON t2.id = t1.id
SET t2.some_time = NOW()
WHERE t1.id IS NULL