Move records from 1 table to another And Including 1 field

[bdichiara]

I've created a table called `temp` and I want to move all the rows in `temp` to `songs` based on:
`temp`.`title` -> `songs`.`title`
`temp`.`artist` -> `songs`.`artist`
`temp`.`catalog` -> `songs`.`catalog`
NOTHING -> `songs`.`userid`

There is no field in the temp table that has this value, i will need to insert it along with each record. How would I go about doing that, without looping through 1 query and inserting each as I go?


_______________
_brian.

 

[johnwm]

Just set songs.userid to allow NULL or set a default value of empty string. Then do the insert without specifying a value for userid

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[bdichiara]

It's not a matter of finding a way to get the records into the table, but more of a question as to HOW I can insert the records from 1 table into another AND include 1 field additionally.

_______________
_brian.

 

[towerbase]

If your version of MySQL supports sub selects then something like the following might be suitable. If your version of MySQL doesn't support sub selects then it might be time to upgrade.

INSERT INTO songs ( title, artist, catalog,  userid ) SELECT ( title, artist, catalog, null ) FROM temp

Andrew
Hampshire, UK

 

[bdichiara]

But how do I put a value into that last field?

_______________
_brian.

 

[towerbase]

I thought that you wanted NOTHING in this field which in SQL talk is NULL.

I think you need to supply more information. What tools are you using? Where does the userid information come from? Does the table have a primary key (e.g. catalog)?

Andrew
Hampshire, UK

 

[bdichiara]

In my example, the only reason I had NOTHING in the "diagram" was to show that the original table did not have a value I wanted to copy over with the records into the userid field. I do however would like to add a value for the new field, based on the current logged in user.

so i guess to better explain, here is my updated 'diagram':

`temp`.`title` -> `songs`.`title`
`temp`.`artist` -> `songs`.`artist`
`temp`.`catalog` -> `songs`.`catalog`
$loggedInUserId -> `songs`.`userid`

_______________
_brian.

 

[towerbase]

This is a good game. I ask what tools you are using and instead of giving me an answer you give clues that it might be PHP (but maybe it is Perl) because of the $.

Let's guess it is PHP. So have you tried something like:

$command="INSERT INTO songs ( title, artist, catalog,  userid ) SELECT ( title, artist, catalog, '$loggedInUserId' ) FROM temp";
mysql_query($command);

?

Andrew
Hampshire, UK

 

[bdichiara]

No, but that's exactly what I was looking for. Sorry about not being clear on my question/tools. Thanks for the help.

_______________
_brian.

 

[johnwm]

My apologies - I should have answered the question you meant to ask and not the question you did ask!

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints