Jump to content

after a little help in moving data from one table to another.


jasonc
 Share

Recommended Posts

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
Share on other sites

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
Share on other sites

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
Share on other sites

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
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.