Professional Web Applications Themes

Suggestions needed - MySQL

Given two tables, I need to insert each record in the first into the second that isn't already in the second, and I need to insert the CURRENT_TIMESTAMP in a specific field in the second if it is not in the first. (in fact, the first table is temporary) 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); I am not quite sure how best to structure the statement that will accomplish the second. Perhaps something ...

  1. #1

    Default Suggestions needed

    Given two tables, I need to insert each record in the first into the
    second that isn't already in the second, and I need to insert the
    CURRENT_TIMESTAMP in a specific field in the second if it is not in the
    first. (in fact, the first table is temporary)

    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);

    I am not quite sure how best to structure the statement that will
    accomplish the second. Perhaps something like:

    INSERT INTO tbl_2_name (some_time)
    SELECT NOW()
    WHERE tbl_2_name.id NOT IN (SELECT id FROM tbl_1_name);

    But alas, I recall reading somewhere that the statement above isn't
    supported in MySQL. If that is true of some versions of MySQL, please
    tell me it is no longer true. :-(

    Is there a better way?

    Thanks,

    Ted

    Ted Guest

  2. #2

    Default Re: Suggestions needed

    Ted wrote:
    > 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);
    Actually, you can't do that in any version of MySQL, because they have a
    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.

    See [url]http://dev.mysql.com/doc/refman/5.0/en/insert.html[/url]
    > 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);
    I'm not sure I understand what you mean here. Do you want to INSERT
    _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

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Suggestions needed

    Thanks Bill.

    Yes, you're right. I meant UPDATE rather than INSERT.

    I guess, though, it is a good thing I continued experimenting while
    awaiting replies. Consider the following two pairs of statements.
    Both work, doing what I was intending to do.

    INSERT INTO tbl_2 (id,some_data)
    SELECT id,some_data FROM tbl_1
    WHERE tbl_1.id NOT IN (SELECT id FROM tbl_2);


    UPDATE tbl_2
    SET tbl_2.some_date = NOW()
    WHERE tbl_2.id NOT IN (SELECT id FROM tbl_1);

    ================================================== ====

    INSERT IGNORE INTO tbl_2 (id,some_data) SELECT id,some_data FROM tbl_1;

    UPDATE tbl_2 t2 LEFT JOIN tbl_1 t1 ON t2.id = t1.id
    SET t2.some_date = NOW()
    WHERE t1.id IS NULL AND t2.some_date IS NULL;

    Had I read your response before I tried the first pair of statements, I
    wouldn't have tried them. But they work. Is that a fluke? Have I
    encountered a bug in MySQL? Or has the limitation you mentioned been
    removed in the relatively recent releases of MySQL (I'm using 5.0.19)?

    Assuming what I've tried is supposed to work as I wanted, there is the
    question of which approach will be more efficient with large tables.
    The table that I'd be using this on, once in production, has several
    dozen fields and tens of thousands of records.

    Thanks again.

    Ted

    Ted Guest

  4. #4

    Default Re: Suggestions needed

    Bill Karwin wrote:
    > Ted wrote:
    >> 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);
    >
    > Actually, you can't do that in any version of MySQL, because they have a
    > 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.
    This limitation does not exist anymore in the current versions:

    From: [url]http://dev.mysql.com/doc/refman/5.0/en/insert-select.html[/url]

    The target table of the INSERT statement may appear in the FROM clause
    of the SELECT part of the query. (This was not possible in some older
    versions of MySQL.)

    Greetings
    Kai

    --
    This signature is left as an exercise for the reader.
    Kai Ruhnau Guest

  5. #5

    Default Re: Suggestions needed

    Kai Ruhnau wrote:
    > From: [url]http://dev.mysql.com/doc/refman/5.0/en/insert-select.html[/url]
    >
    > The target table of the INSERT statement may appear in the FROM clause
    > of the SELECT part of the query. (This was not possible in some older
    > versions of MySQL.)
    Well I'll be a monkey's uncle! :-)

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Dreamweaver suggestions needed
    By That Guy 13 in forum Macromedia Freehand
    Replies: 2
    Last Post: July 26th, 07:13 PM
  2. A WebService as Data Access Layer - Suggestions needed
    By Timothy Parez in forum ASP.NET Web Services
    Replies: 3
    Last Post: July 18th, 08:32 AM
  3. need suggestions+help
    By GilesW in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: April 20th, 10:09 AM
  4. sharing databases - suggestions needed
    By rnd in forum FileMaker
    Replies: 2
    Last Post: September 19th, 02:48 AM
  5. Any suggestions??
    By jln32 in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 18
    Last Post: July 11th, 08:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139