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
statement:

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 
ItemID.

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" 
><one.point.six@stripped>
>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 
>>thehttp://by117fd.bay117.hotmail.msn.com/cgi-bin/compose?&curmbox=FB54776F%2dC57C%2d4DCB%2dA84B%2d7F6C01495042&a=d4ad23bf331d111b37a1ca3291dc8b63f9d0a513e78101aa6abf0a85ce8c3745#
>>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 
>>http://www.reallivemoms.ca/
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>

_________________________________________________________________
Have Some Fun Out Of The Sun This March Break 
http://local.live.com/?mkt=en-ca/?v=2&cid=A6D6BDB4586E357F!142

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