List:General Discussion« Previous MessageNext Message »
From:Miles Thompson Date:March 12 2007 7:44pm
Subject:Re: INSERT ... SELECT Challenge
View as plain text  
So with a unique index on ItemI + AttributeID + Attribute_Value, this could 
be the

INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value) IGNORE
  SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1, '31', 
'default text';

which should result in a new row containg '31' and 'default text' for every 

Never thought of this approach - innovative.

Regards - Miles

>From: "Brent Baisley" <brenttech@stripped>
>Skip the whol SELECT part an create a unique index on the fields you want 
>unique (AttributeID, Attribute_Value). Then just do an INSERT IGNORE. The 
>index will prevent a new non-unique from being entered and the IGNORE will 
>prevent an error.
>----- Original Message ----- From: "Miles Thompson" 
>To: <mysql@stripped>
>Sent: Monday, March 12, 2007 3:02 PM
>Subject: INSERT ... SELECT Challenge
>>I want to add records to an attributes table for every item which does not 
>>have an attribute of a given value. The problem is that some records have 
>>already had these values added.
>>Please have a look at the following query, an INSERT ... SELECT construct 
>>which I believe will do the job:
>>INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value)
>>   SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1
>>   WHERE bmIA1.ItemID NOT IN
>>     ( SELECT DISTINCT bmIA2.ItemID from bm_ItemsAttributes bmIA2
>>        WHERE bmIA2.AttributeID BETWEEN '31' AND '33' ), '31', 'test val';
>>The fields in the bm_ItemsAttributes table are ItemID, AttributeID and 
>>Attribute_Value and there is no primary key or autoincrement.
>>The SELECT query and its sub-query return a list of unique ItemID's which 
>>do not have any AttributeID's between 31 and 33.
>>I plan to run the query again to insert '32' and then '33'. I suppose one 
>>could build this into a stored procedure and 
>>Sendn loop  through each,
>>Any observations will be welcomed. (Jay will probably figure out a much 
>>more elegant way, but I'm pretty proud to have gotten this far.)
>>Cheers - Miles
>>RealLiveMoms: Share your experience with Real Live Moms just like you 
>>MySQL General Mailing List
>>For list archives:
>>To unsubscribe:

Have Some Fun Out Of The Sun This March Break!142

INSERT ... SELECT ChallengeMiles Thompson12 Mar
  • Re: INSERT ... SELECT ChallengeBrent Baisley12 Mar
    • Re: INSERT ... SELECT ChallengeMiles Thompson12 Mar
    • Re: INSERT ... SELECT ChallengeMiles Thompson12 Mar