0

I'm hoping I'm just being an idiot here, but I've got a query that truncates a table, then selects all possible combinations of some foreign keys and inserts them to make a profile.

I've got 2 x 2 x 3 x 4 x 7 keys to select, which comes to 336 distinct combinations.

If I run this select, I get 336 rows:

SELECT DISTINCT
    `contract_agreement_type`.`id`, 
    `contract_deposit`.`id`,
    `contract_length`.`id`, 
    `contract_maintenance_type`.`id`,
    `contract_mileage`.`id`
FROM `contract_agreement_type`, 
    `contract_deposit`,
    `contract_length`, 
    `contract_maintenance_type`,
    `contract_mileage`

Woohoo, that's great! But when I insert it, I ended up with 672 rows?! To check my maths was right, I added an index on the 5 columns, and when I run it again, I get a duplicate key error:

INSERT INTO `contract_profile`
    (
        `contract_agreement_type_id`, 
        `contract_deposit_id`,
        `contract_length_id`, 
        `contract_maintenance_type_id`,
        `contract_mileage_id`
    )
    SELECT DISTINCT
        `contract_agreement_type`.`id`, 
        `contract_deposit`.`id`,
        `contract_length`.`id`, 
        `contract_maintenance_type`.`id`,
        `contract_mileage`.`id`
    FROM `contract_agreement_type`, 
        `contract_deposit`,
        `contract_length`, 
        `contract_maintenance_type`,
        `contract_mileage`

# Duplicate entry '1-3-24-1-5' for key 'profile'

So the select is fine, but the insert duplicates the data somehow - what have I done wrong?!

Pete
  • 4,542
  • 9
  • 43
  • 76
  • are you inserting it through any front end or directly in database? – DevelopmentIsMyPassion Jan 15 '15 at 12:32
  • Tried with SequelPro (Mac client) and via a Coldfusion CFScript page - neither work. I'll try cmd... tried it, same error – Pete Jan 15 '15 at 12:33
  • Perhaps the table already contains data. – Gordon Linoff Jan 15 '15 at 12:37
  • I run truncate first so it's always empty first. To confirm I've just deleted the contents of the table and run the query, same error again. – Pete Jan 15 '15 at 12:38
  • Ha, I just realised it actually inserts the first 336 fine, so I could carry on but I don't want to have to put in a hacky try/catch to ensure it's worked – Pete Jan 15 '15 at 12:39
  • You are not joining your tables. You are create a cross join between all three tables. I'm pretty sure this is not what you intend to do –  Jan 15 '15 at 12:40
  • But if I select without doing the join, I get the expected 336 rows. What is it about the INSERT that duplicates the SELECT? – Pete Jan 15 '15 at 12:41
  • Also I can't join these 5 tables - they have no reference to each other, they're really low level data. `contract_mileage` just lists the available mileage values and an id - the rest are all the same, they then link to a central pricing table. I'd have to add that central table to do a join which seems unnecessary, however it might be the only way to get it to work without an error – Pete Jan 15 '15 at 12:43
  • Right, I have a workaround which I will post when I can answer myself in 2 days - I'm using a Natural Primary key, which is still an integer, which is a concatenation of each field and using `REPLACE INTO` instead – Pete Jan 15 '15 at 14:00

0 Answers0