Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: duplicate multiple rows - but certain cols still custom [SOLVED]

    I doubt this is possible, and I am successfully doing this using a php file right now, but im wondering if this is possible to do completely in sql

    So say I have a table with say 6 rows (id, sid, file, file_version, timeStamp).
    I want to duplicate the rows while changing the id, sid, and time

    So basically I want to create X new rows, that share the file, and file_version with the original X

    Code:
    id	sid	file	file_version	time
    //have
    1	4	2	38		2009-07-12 19:42:59
    2	4	13	46		2009-07-12 19:42:59
    3	4	56	59		2009-07-12 19:42:59
    4	4	78	72		2009-07-12 19:42:59
    5	4	212	117		2009-07-12 19:42:59
    6	4	300	190		2009-07-12 19:42:59
    
    //want to add
    7	13	2	38		2009-07-13 22:04:02
    8	13	13	46		2009-07-13 22:04:02
    9	13	56	59		2009-07-12 22:04:02
    10	13	78	72		2009-07-12 22:04:02
    11	13	212	117		2009-07-12 22:04:02
    12	13	300	190		2009-07-12 22:04:02

    ------------------------------SOLVED WITH------------------------------

    Quote Originally Posted by r937
    temp table is not necessary
    Code:
    INSERT 
      INTO daTable
         ( sid  
         , file 
         , file_version  
         , time )
    SELECT 13      -- new sid
         , file
         , file_version  
         , CURRENT_TIMESTAMP
      FROM daTable
     WHERE sid = 4  -- sid being copied
    Last edited by ameyer; 07-14-09 at 09:23.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Try something like this:
    - create a temp table
    - INSERT INTO temp_table
    SELECT new_id, new_sid, file, file_version, time FROM original_table
    - INSERT INTO original_table
    SELECT * FROM temp_table
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    temp table is not necessary
    Code:
    INSERT 
      INTO daTable
         ( sid  
         , file 
         , file_version  
         , time )
    SELECT 13      -- new sid
         , file
         , file_version  
         , CURRENT_TIMESTAMP
      FROM daTable
     WHERE sid = 4  -- sid being copied
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2009
    Posts
    32
    WOW man! You are good!
    Thank you so much.


    Quote Originally Posted by r937
    temp table is not necessary
    Code:
    INSERT 
      INTO daTable
         ( sid  
         , file 
         , file_version  
         , time )
    SELECT 13      -- new sid
         , file
         , file_version  
         , CURRENT_TIMESTAMP
      FROM daTable
     WHERE sid = 4  -- sid being copied

Posting Permissions

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