www.webdeveloper.com
Results 1 to 3 of 3

Thread: INSERT based on another record's data

Hybrid View

  1. #1
    Join Date
    Jul 2009
    Location
    Québec
    Posts
    21

    Question INSERT based on another record's data

    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

  2. #2
    Join Date
    Sep 2010
    Posts
    160
    Hello,

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

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

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    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.
    Code:
    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).
    Last edited by eval(BadCode); 11-21-2010 at 03:36 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles