SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Insert random data into DB using INSERT INTO SELECT

    Hello all,

    I need to pass data from one table to another, with previous help from this community, I get something like:

    Code MySQL:
    INSERT INTO newDatabase.newTable(name, description) SELECT old_name FROM oldDatabase.oldTable WHERE old_id > 2;
    This works perfectly.

    Now, the point is, not only to insert data from the old table but, as well, populate (is this the correct term?) specific table columns with random data.

    However, this random data on those table columns, should be different on each new row created.


    I believe something like this will take care of the random part:
    Code PHP:
    $randomValue = mb_substr(md5(uniqid()), 0, 8);

    And I was hopping that something like this could take care of the job:
    Code MySQL:
    INSERT INTO newDatabase.newTable(name, description) VALUES ((SELECT old_name FROM oldDatabase.oldTable WHERE old_id > 2), $randomValue;

    1) I'm getting a query error: "1242 - Subquery returns more then 1 row".

    2) I'm not aware of the php logic that will allow me to have different randomValue values, each time new rows are inserted.


    Can I have your help please?

    Thanks in advance,
    Márcio

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oikram View Post
    This works perfectly.
    um, no it doesn't

    the INSERT column list contains two columns (name,description), whereas the SELECT contains only one (old_name)

    to insert random data, you might consider using the UUID mysql function

    Code:
    INSERT 
      INTO newDatabase.newTable
         ( name
         , description ) 
    SELECT old_name 
         , UUID() /* and optionally some functions on this */
      FROM oldDatabase.oldTable 
     WHERE old_id > 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    Quote Originally Posted by r937 View Post
    um, no it doesn't

    the INSERT column list contains two columns (name,description), whereas the SELECT contains only one (old_name)
    You are right. I was editing the code and I end you with incorrect syntax.

    Quote Originally Posted by r937 View Post
    to insert random data, you might consider using the UUID mysql function
    I was just reading about it on gooracle. However, the destination table column as some length limitations, hence, I need a max of 8 length string. (because that value will be used on the URL I suppose).

    Could this be done with the optionally functions you were talking about?

    One of the random fields accept only digits of 6 length size, can the UUID generate only digits as well ?
    EDIT - Yes, by using UUID_SHORT(), however, it's not 6 digits length.

    Additional Notes: I only need to do this for migration proposes, for 5.000 or so, records. Once this is done, the new records will be inserted using the system that, I suppose, has is own ways to avoid duplicates. (I cannot see, neither edit, the php new system files).


    Thanks again,
    Márcio

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oikram View Post
    Could this be done with the optionally functions you were talking about?
    with some finagling, sure

    try MID(UUID(),3,6) for a 6-byte string

    for a 6-digit number, try FLOOR(RAND()*1000000)

    keep experimenting, keep testing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will. I'm being introduced to mySQL functions, and the subsequent power, right now, with your post. I thought the joins would be the hard we can get. mySQL is really powerfull.

    I will give it a try.


    Thanks a lot,
    Márcio

  6. #6
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,

    I'm struggling with the possibilities again.

    On the old table we have some int values that, on the new table should correspond a specific string value. Is there a way to make this correspondence on the query already in use?


    If we have something like this, it will not work as expected, because the WHERE clause is not column specific, right?

    Code MySQL:
    INSERT INTO newDatabase.newTable(name, status)
    SELECT old_name, 'specific_string_status'
    FROM oldDatabase.oldTable
    WHERE old_status = 2

    I can do the insert, with the old values to this new table, and then, make an UPDATE WHERE clause to convert the numeric values to string ones but, I was just wondering if this can be done with one INSERT at once.

    thanks in advance,
    Márcio

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oikram View Post
    If we have something like this, it will not work as expected, because the WHERE clause is not column specific, right?
    i don't understand this part

    the WHERE clause is very specific -- old_status must equal 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    He wants to know how to replace 2 with a string (without using the where). ie, case 2 string "two", case 3 "three".
    I'd have to google tbh, not something I do daily.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ah, i get it (just a bit slow this morning)

    okay, all you need is a table of statuses and their translations

    you can add this table to the query "on the fly" --
    Code:
    INSERT 
      INTO newDatabase.newTable
         ( name
         , status )
    SELECT t.old_name
         , x.status_string
      FROM oldDatabase.oldTable AS t
    INNER
      JOIN ( SELECT 1                     AS status
                  , 'string for status 1' AS status_string
             UNION ALL
             SELECT 2
                  , 'string for status 2'
             UNION ALL
             SELECT 3
                  , 'string for status 3'
             ...
           ) AS x
        ON x.status = t.old_status
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And you say you were slow this morning hm?! Oh well...

    I'm trying to understand this query for the last hours, lets see:

    Code MySQL:
    INSERT 
      INTO newDatabase.newTable
         ( name
         , status )
    SELECT t.old_name
         , x.status_string 
    FROM oldDatabase.oldTable AS t

    We are saying: Insert into the newTable columns 'name' and 'status', the result of the SELECT instruction that will grab the oldTable column 'old_name' and the x (temporary table alias?) status_string (temporary column of the temporary table created) ?

    When do we actually create that table? When we do the INNER JOIN with the AS clause?
    Code MySQL:
    INNER JOIN (...) AS x

    What are the numbers on SELECT 1 SELECT 2 here:
    Code MySQL:
    SELECT 1 AS status, 'string for status 1' AS status_string
     
    UNION ALL
     
    SELECT 2, 'string for status 2'
     
    UNION ALL
     
    SELECT 3, 'string for status 3')

    I was expecting that instead of 1 we had a column name.


    Can I have a push on your query suggestion please?



    Regards,
    Márcio

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oikram View Post
    Can I have a push on your query suggestion please?
    i think you have understood my query very well

    just run the subquery by itself and you will see that it creates a result consisting of two columns, where the first column name is status and the second column name is status_string...

    ... exactly as if we had created a table for this purpose!

    this use of a subquery in the FROM clause is called a derived table

    the derived table can be used in the outer query exactly as if it were a "real" table -- it ~is~ real, it just isn't permanent, it exists only for the duration of the query

    note that the column names for the derived table are assigned in the first SELECT of the UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i think you have understood my query very well

    just run the subquery by itself and you will see that it creates a result consisting of two columns, where the first column name is status and the second column name is status_string...
    Ok.

    Quote Originally Posted by r937 View Post
    this use of a subquery in the FROM clause is called a derived table
    I will search about it, for sure. thanks.

    Quote Originally Posted by r937 View Post
    the derived table can be used in the outer query exactly as if it were a "real" table -- it ~is~ real, it just isn't permanent, it exists only for the duration of the query
    Clear. Ok.

    Quote Originally Posted by r937 View Post
    note that the column names for the derived table are assigned in the first SELECT of the UNION
    So that's why the AS exists only on the first Union statement, as the mysql documentation suggests as well.

    But what about the numbers? SELECT 1, SELECT 2?

    I have test the subquery only, and I've changed the values from select 2 to select 4, and I realize that the 1, 2 etc... are actually values on our temp table columns with the alias of status (declared on the first Select of the UNION).

    If we change SELECT 1 to SELECT 'hello' it will put this string as a value of the column status.

    But I'm not understating, how/what is this 1 in the following context, is not a column, is not a row, it's a value, but after a SELECT keyword? How?

    In MySQL website, I can read:
    SELECT can also be used to retrieve rows computed without reference to any table.
    and the example:
    Code MySQL:
    mysql> SELECT 1 + 1;
            -> 2

    but we are not talking about rows here, are we?


    Thanks again,
    Márcio

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oikram View Post
    Code MySQL:
    mysql> SELECT 1 + 1;
            -> 2
    but we are not talking about rows here, are we?
    actually, yes we are

    that query returns one row consisting of one unnamed column

    of course, the column ~does~ have a name (every column has a name), and i believe the default name, since you did not assign one, is `1 + 1`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    by the way, other database systems have different ways of assigning column names to expressions

    for example, Expr1001, Expr1002...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    actually, yes we are

    that query returns one row consisting of one unnamed column

    of course, the column ~does~ have a name (every column has a name), and i believe the default name, since you did not assign one, is `1 + 1`
    With the value of 2 .
    ?

    So, if we run a query like the above:
    Code MySQL:
    SELECT 1 AS status, 'string for status 1' AS status_string
    UNION ALL
    SELECT 2, 'string for status 2'
    UNION ALL
    SELECT 3, 'string for status 3'

    I can see on the output window of Workbench the following structure:
    status-----status_string
    ----1------string for status 1
    ----2------string for status 2
    ----3------string for status 3

    So, can we say, about the 1 that appears after the status of this graphical representation of the resultset that:
    1 is a value of this temporary result set returned, on the temporary column status at row 1 ?

    2 is a value of this temporary result set returned, on the temporary column status at row 2 ?


  16. #16
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All together now:


    Code MySQL:
    INSERT INTO gwebhspt_gestaowhmcs.tbltickets(id, did, tid, c, userid, message,  date, title, urgency, flag, clientunread, adminunread, replyingadmin, status)
    SELECT alp.ticket_id, alp.topic_id, FLOOR(RAND()*1000000), MID(UUID(),1,8), alp.cust_id, alp.ticket_text, alp.ticket_date, alp.ticket_subject, 'Medium', 0, 0, 1, 0, x.status_string
    FROM gwebhspt_testesmm.support_topics AS alp
    INNER JOIN (
                SELECT 0 AS status, 'Answered' AS status_string
                UNION ALL
                SELECT 1, 'Customer-Reply'
                UNION ALL
                SELECT 3, 'Closed')
           AS tmpApl
    ON tmpApl.status = alp.ticket_status

    I'm getting a "Unknow column in field list" - I have checked and rechecked, and is there. If I start removing just to see if the error stops occuring no matter what, I end of with the same message for x.status_string.

    Any advice please?


    Best Regards,
    Márcio

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    in the outer SELECT clause, you refer to x.status_string, but there is no table called x in your query

    there is, however, a table called tmpApl

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for checking it.

    The error was still there, but after 1 hour searching how can I get a command line version of the SHOW CREATE TABLE using Workbench (that actually I was unable to found), and then been able to see the lines of SHOW CREATE TABLE using mySQL Query Browser (deprecated by the above), I end up re-looking for both tables and one of the tables names on the query was not correct.

    I will see the query consequences now, but it works, I've learn a little bit more, and I'm waiting for a "Simple SQL" to give me some lights on SELECT 1.


    Best regards, and really, thanks for your time and guidance,
    Márcio


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
  •