Professional Web Applications Themes

insert into...select - MySQL

I'm working with mysql 5.0.27 and I want to duplicate a row in the same table... so i tried this query: CREATE TABLE `test` ( `testId` int(10) NOT NULL auto_increment, `testNameNl` varchar(150) NOT NULL default '', `testNameFr` varchar(150) NOT NULL default '', `testNameEn` varchar(150) NOT NULL default '', `testTextNl` text NOT NULL, `testTextFr` text NOT NULL, `testTextEn` text NOT NULL, `testMinimum` decimal(2,0) NOT NULL default '0', `testOrder` tinyint(3) NOT NULL default '1', `sspprofileId` smallint(5) unsigned NOT NULL default '0', `testDate` int(14) NOT NULL default '0', `projectId` int(10) NOT NULL default '0', PRIMARY KEY (`testId`), KEY `projectId` (`projectId`) ) ENGINE=InnoDB DEFAULT ...

  1. #1

    Default insert into...select

    I'm working with mysql 5.0.27 and I want to duplicate a row in the same
    table... so i tried this query:

    CREATE TABLE `test` (
    `testId` int(10) NOT NULL auto_increment,
    `testNameNl` varchar(150) NOT NULL default '',
    `testNameFr` varchar(150) NOT NULL default '',
    `testNameEn` varchar(150) NOT NULL default '',
    `testTextNl` text NOT NULL,
    `testTextFr` text NOT NULL,
    `testTextEn` text NOT NULL,
    `testMinimum` decimal(2,0) NOT NULL default '0',
    `testOrder` tinyint(3) NOT NULL default '1',
    `sspprofileId` smallint(5) unsigned NOT NULL default '0',
    `testDate` int(14) NOT NULL default '0',
    `projectId` int(10) NOT NULL default '0',
    PRIMARY KEY (`testId`),
    KEY `projectId` (`projectId`)
    ) ENGINE=InnoDB DEFAULT CHT=utf8 AUTO_INCREMENT=15 ;


    INSERT INTO test
    SELECT * FROM test AS testsource WHERE testsource.testId = '15'
    ON DUPLICATE KEY UPDATE testId = 'newautoincrement?' , projectId = '8',
    testOrder = '2'

    I'm having 2 problems with this:

    1. the database doens't duplicate the row, instead it just changes the
    original row.
    2. how can I determine the new autoincrement value, or let it update
    automatically. Remember I do need the SELECT * , because the tables can
    change sometimes (extra fields can be added).

    Anyone a solution?

    gehegeradeaus@gmail.com Guest

  2. #2

    Default Re: insert into...select

    > INSERT INTO test 

    This is exactly what the command does. If a uniquenes constraint is
    violated, the existing row gets updated
     

    You can set the autoincrement column to NULL or just omit it. 

    INSERT <list all the fields, except for the testId> SELECT <the same
    list> FROM testsource WHERE testId=15

    This should do it.
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  3. #3

    Default Re: insert into...select



    On 25 jan, 12:57, Willem Bogaerts
    <maardanzonderditstuk.nl> wrote: 


    > violated, the existing row gets updated

    >
    >

    > list> FROM testsource WHERE testId=15
    >
    > This should do it.
    > --
    > Willem Bogaerts
    >
    > Application smith
    > Kratz B.V.http://www.kratz.nl/[/ref]

    Thanks,

    I thought it would update the new row instead of the original one... a
    little bit confusing, but I solved it now.

    gehegeradeaus@gmail.com Guest

  4. #4

    Default Re: insert into...select



    On 25 Jan, 13:33, com wrote: [/ref]
    > [/ref]

    > > violated, the existing row gets updated[/ref]
    > [/ref]

    > > list> FROM testsource WHERE testId=15[/ref]


    >
    > I thought it would update the new row instead of the original one...[/ref]
    You did???
    When I read in the manual:

    "If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that
    would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an
    UPDATE of the old row is performed."

    I took the statement "an UPDATE of the old row is performed" to mean
    that the old row would be updated. How is such a plain statement
    confusing?
     

    Captain Guest

Similar Threads

  1. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  2. Select from one datasource insert into another
    By StokeyTCI in forum Coldfusion - Advanced Techniques
    Replies: 5
    Last Post: September 13th, 02:49 PM
  3. SELECT and INSERT
    By McKirahan in forum ASP Database
    Replies: 4
    Last Post: August 2nd, 05:45 PM
  4. Insert from Select
    By RPhillips@ce-a.com in forum Informix
    Replies: 6
    Last Post: July 30th, 10:16 PM
  5. How to select and then loop while insert
    By Mansoor Aleem in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 8th, 08:20 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139