I need to pass data from one table to another, with previous help from this community, I get something like:
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:
$randomValue = mb_substr(md5(uniqid()), 0, 8);
And I was hopping that something like this could take care of the job:
INSERT INTO newDatabase.newTable(name, description) VALUES ((SELECT old_name FROM oldDatabase.oldTable WHERE old_id > 2), $randomValue;
I’m getting a query error: “1242 - Subquery returns more then 1 row”.
I’m not aware of the php logic that will allow me to have different randomValue values, each time new rows are inserted.
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
INSERT
INTO newDatabase.newTable
( name
, description )
SELECT old_name
, UUID() /* and optionally some functions on this */
FROM oldDatabase.oldTable
WHERE old_id > 2
You are right. I was editing the code and I end you with incorrect syntax.
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).
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.
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?
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.
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.
okay, all you need is a table of statuses and their translations
you can add this table to the query “on the fly” –
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
And you say you were slow this morning hm?! Oh well… (:
I’m trying to understand this query for the last hours, lets see:
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?
INNER JOIN (...) AS x
What are the numbers on SELECT 1 SELECT 2 here:
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?
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
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.
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 ?
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.
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
mySQL is really powerfull.