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

[jasonc]

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.

[awjudd]

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

[The Little Guy]

This will work too:

 

CREATE TABLE newTable
SELECT f1, f2, f3
FROM links

[jasonc]

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

[Muddy_Funster]

you can use joins in subquerie selects for INSERT.

[awjudd]

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

[jasonc]

gosh that was fun, got there in the end, all thanks to the hints you all gave.

 

 

[jasonc]

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

[fenway]

UPDATE `links`

LEFT JOIN `accounts` as a ON a.username=l.author

SET `links`.`author` = `a`.`id`

 

 

 

[jasonc]

thank you fenway...

 

but i get an error

 

Unknown column 'l.author' in 'on clause'

[fenway]

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`

 

[jasonc]

yep that worked, thanks again.