Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Problem with complex Insert containing a Subquery

    Hello,

    I have a problem with a complex Insert statement. I want to write data from a source table (bookings_creditcards) from one database (bookings_xml) into a target table (cache_creditcards) into another database (bookings). The source table doesn't have a single unique field (only the combination of 2 fields is unique). Theese fields are creditcard_id and hotel_id. Both fields can occur several times each other, only the combination of them must be unique. It's not possible to use an autoincrement id field for that, because there can be missing rows in the source table so that the match wouldn't work. Because of that I need to insert the data from all rows from the source table into the target table where a specific combination of creditcard_id and hotel_id is not found in the target table.

    The following query works, but checks for 1 field only (creditcard_id in this case):
    Code:
    INSERT INTO bookings.cache_creditcards (creditcard_id, hotel_id)
    SELECT bookings_xml.bookings_creditcards.creditcard_id,
    bookings_xml.bookings_creditcards.hotel_id
    FROM bookings_xml.bookings_creditcards
    WHERE bookings_xml.bookings_creditcards.id NOT IN (
    SELECT bookings.cache_creditcards.id
    FROM bookings.cache_creditcards, bookings_xml.bookings_creditcards
    WHERE bookings.cache_creditcards.id = bookings_xml.bookings_creditcards.id
    );
    The problem is that I have no idea how to integrate the check for hotel_id too into that query. I can't just add another where-clause because the query would check for creditcard_id and hotel_id separately, but I need a check for the combination of both fields. I hope the problem is relatively clear now. Thanks for your help.

    Lars

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    I think MySQL supports the following syntax:

    Code:
    SELECT 
        * 
    FROM foo
    WHERE 
        (id1, id2) IN  (SELECT id1, id2 FROM foo2);
    --
    felix

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    Thanks a lot. I'll try that tomorrow.

  4. #4
    Join Date
    Oct 2004
    Posts
    4
    Ok, I just tried you syntax and it works great. Thanks again.

Posting Permissions

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