copy one column to another

[NamelessNumberheadMan]

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

[r035198x]

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

Which one do you want to copy into which one?
If you're copying SMappingID into SID then you can do
[CODE=sql]update Mapping set SID = SMappingID where SMappingID is not null;[/CODE]

[pbmods]

Heya, NamelessNumberh eadMan.

Your code attempts to set multiple values to a single column (all the values that are not null get saved into each row).

You can do this: [code=mysql]INSERT INTO `Mapping` (`SID`) SELECT `SMappingID` FROM `Mapping` WHERE `SMappingID` IS NOT NULL[/code]

Tell us a bit more about what you're trying to accomplish.

[r035198x]

Heya, NamelessNumberh eadMan.

Your code attempts to set multiple values to a single column (all the values that are not null get saved into each row).

You can do this: [code=mysql]INSERT INTO `Mapping` (`SID`) SELECT `SMappingID` FROM `Mapping` WHERE `SMappingID` IS NOT NULL[/code]

Tell us a bit more about what you're trying to accomplish.

You just wanted to say NamelessNumberh eadMan didn't you?

[NamelessNumberheadMan]

You just wanted to say NamelessNumberh eadMan didn't you?

Appolgies for any lack in clarity. I need to copy SMappingID to SID where SMappingID is not null. Then I need to drop the SMappingID column. I'm no DB expert, and was under the impression this would be more complex and require a loop with IFs and Cases. I haven't had the chance to try out the solution yet, but I'll let you know if/when it works.

- Thanks

[r035198x]

Appolgies for any lack in clarity. I need to copy SMappingID to SID where SMappingID is not null. Then I need to drop the SMappingID column. I'm no DB expert, and was under the impression this would be more complex and require a loop with IFs and Cases. I haven't had the chance to try out the solution yet, but I'll let you know if/when it works.

- Thanks

No need for apologies. I just think that your user name is e-r original.