INSERT based on another record's data

[marcosql]

OK, maybe I didn't describe my problem the right way, or I am now making some progress figuring it out by myself...

Here's my need:

I have a table, called attributes, which contains just 3 fields:

userid, attributeid and value

I have records holding data that I want to split and create other records with, using an INSERT statement. Here's an example, from this record :

userid ; attributeid ; value
1 ; 14 ; 2000-11-29

I want to AUTOMATICALLY create these 2 records (in one or 2 queries, I don't mind) :

userid ; attributeid ; value
1 ; 33 ; 11 (or November)
1 ; 34 ; 29

Of course, if I have 100 records with attribute '14' set to a date, I want to be able to create 200 records (with attribute 33/34) in a single or 2 queries at the most.

I am guessing that is feasible in SQL, but I read that 'WHERE' clause cannot be used in an INSERT statement. How can I achieve the same goal ?

Any help will be greatly appreciated.

Thanks in advance.

Marco

[zimonyi]

Hello,

I am guessing this is an extension of your other post but I will answer in this one.

-- The below SQL inserts the userid, attribute id 33 and the month value for
-- all fields where the userid exists as having a row with attributeid 14
-- but not previously having a row with attributeid 33 (which is what you
-- want to add)
-- NOTE: If you want the name of the month you can use the function
-- MONTHNAME() instead
INSERT INTO attributes a (userid,attributeid,value)
SELECT a2.userid,33,MONTH(value) FROM attributes a2
WHERE a2.userid in (SELECT a3.userid FROM attributes a3 WHERE attributeid = 14) AND NOT a2.userid IN (SELECT a4.userid FROM attributes a4 WHERE attributeid = 33)

-- The below SQL inserts the userid, attribute id 33 and the day value for
-- all fields where the userid exists as having a row with attributeid 14
-- but not previously having a row with attributeid 33 (which is what you
-- want to add)
INSERT INTO attributes a (userid,attributeid,value)
SELECT a2.userid,34,DAY(value) FROM attributes a2
WHERE a2.userid in (SELECT a3.userid FROM attributes a3 WHERE attributeid = 14) AND NOT a2.userid IN (SELECT a4.userid FROM attributes a4 WHERE attributeid = 34)

I hope they work.

Archie

[eval(BadCode)]

userid ; attributeid ; value
1 ; 14 ; 2000-11-29

I want to AUTOMATICALLY create these 2 records (in one or 2 queries, I don't mind) :

userid ; attributeid ; value
1 ; 33 ; 11 (or November)
1 ; 34 ; 29

If attributeid is unique to userid then I would create a unique index on these 2 columns.

INSERT IGNORE INTO attributes (attributes.userid, attributes.attributeid, attributes.value) VALUES 
(SELECT attributes1.userid, 33, attributes1.MONTH(value) from attributes as attributes1),
(SELECT attributes2.userid, 34, attributes2.DAY(value) from attributes as attributes2);

Insert ignore, or where not exists, or where not in.... the same to someone who doesn't care about performance. Archies query makes less sense to me in terms of reading it, but it makes more sense in terms of performance.

Either way, I'd still create that index. Also I don't know how well DAY() and MONTH() will work on a column that isn't the date type (never tried).