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
dBforums
>
Problem with complex Insert containing a Subquery
10-19-05, 07:54
Linear Mode
