jasonc Posted December 30, 2011 Share Posted December 30, 2011 I have a load of links in my table and would like to move them to a new table but under different fields. get all the following details from `links` table. links.id > newTable.id links.url > newTable.url links.desc > newTable.desc links.sourcedesc > newTable.sourcedesc links.sourceurl > newTable.sourceurl links.clicks > newTable.clicks get links.submittedby, look up submittedby in users.username table, get user.id users.id > newTable.author place all newTable entries in to a new table. how would I go about doing this. Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/ Share on other sites More sharing options...
awjudd Posted December 30, 2011 Share Posted December 30, 2011 By using the set based insert. Write your SELECT statement from your first table to get the data like you need it in the destination table (where you are copying to) and then with that SELECT use it as you INSERT. INSERT INTO newTable ( f1, f2, f3 ) SELECT f1, f2, f3 FROM links Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302588 Share on other sites More sharing options...
The Little Guy Posted December 30, 2011 Share Posted December 30, 2011 This will work too: CREATE TABLE newTable SELECT f1, f2, f3 FROM links Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302590 Share on other sites More sharing options...
jasonc Posted December 30, 2011 Author Share Posted December 30, 2011 Ah thanks, that easy. But the only part that I am lost on now is how to get links.submittedby, look up submittedby in users.username table, get users.id so that the users.id can be copied in to newTable.author By using the set based insert. Write your SELECT statement from your first table to get the data like you need it in the destination table (where you are copying to) and then with that SELECT use it as you INSERT. INSERT INTO newTable ( f1, f2, f3 ) SELECT f1, f2, f3 FROM links Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302592 Share on other sites More sharing options...
Muddy_Funster Posted December 30, 2011 Share Posted December 30, 2011 you can use joins in subquerie selects for INSERT. Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302595 Share on other sites More sharing options...
awjudd Posted December 30, 2011 Share Posted December 30, 2011 As Muddy_Funster said, you use joins. Step 1 - write a SELECT query that will get the data how you need it (use joins as needed) Step 2 - use the query from Step 1 and put it into the INSERT Step 3 - sit back watch as the table gets filled Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302603 Share on other sites More sharing options...
jasonc Posted December 30, 2011 Author Share Posted December 30, 2011 gosh that was fun, got there in the end, all thanks to the hints you all gave. Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302608 Share on other sites More sharing options...
jasonc Posted December 30, 2011 Author Share Posted December 30, 2011 oh something went wrong with the convert and caused all the usernames to still be there, made so many other changes and it would take ages to redo from the start. how can i update the usernames so they are having the userid instead this is what i have tried and the first part the selects shows all the userid and usernames correctly but the update part is not working. what am i doing wrong? UPDATE `links` SET `links`.`author` = `a`.`id` SELECT `l`.`author`, `a`.`id`, `a`.`username` FROM `links` as l LEFT JOIN `accounts` as a ON a.username=l.author Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302615 Share on other sites More sharing options...
fenway Posted December 30, 2011 Share Posted December 30, 2011 UPDATE `links` LEFT JOIN `accounts` as a ON a.username=l.author SET `links`.`author` = `a`.`id` Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302617 Share on other sites More sharing options...
jasonc Posted December 30, 2011 Author Share Posted December 30, 2011 thank you fenway... but i get an error Unknown column 'l.author' in 'on clause' Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302622 Share on other sites More sharing options...
fenway Posted December 30, 2011 Share Posted December 30, 2011 Sorry, I was hacking at your two statements -- left in the alias by mistake. UPDATE `links` LEFT JOIN `accounts` as a ON a.username=links.author SET `links`.`author` = `a`.`id` Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302628 Share on other sites More sharing options...
jasonc Posted December 30, 2011 Author Share Posted December 30, 2011 yep that worked, thanks again. Link to comment https://forums.phpfreaks.com/topic/254086-after-a-little-help-in-moving-data-from-one-table-to-another/#findComment-1302637 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.