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" <[EMAIL PROTECTED]>
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]
_________________________________________________________________
Have Some Fun Out Of The Sun This March Break
http://local.live.com/?mkt=en-ca/?v=2&cid=A6D6BDB4586E357F!142
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
