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" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
