MySQL Script to SET new Column Values Based on Another Columns value

Question

I have an existing database with an existing table and in that table I have a column called "packageno" which stands for Package Number. I have over 400 rows of database content. Each Unique row has a Package Number. But Now, I added a column called "packagedescription" which stands for the Package Description. Right now the new column is empty. But I have a legend for the package description in PHP code, for example:

if ($packageno == 1) {
 $description = 'Interior Rooms';
 }
if (packageno == 2) { 
 $description = 'Exterior Rooms';
 }

Etc....

But from the beginning, I didn't add the description. Instead, I added the package number.

So, what I would like to do is UPDATE or SET those empty database fields in the packagedescription column, with the actual description of the package. How would I write a MySQL script to enter the description of all existing rows, using the packageno?

I know a little MySQL but I have never done anything like this to existing rows.

Any Help would be appreciated!

Answer 1

You can also do it in one query:

UPDATE `table_name` SET packagedescription = 
    IF(packageno = 1, 'Interior Rooms',
    IF(packageno = 2, 'Exterior Rooms',
    IF(packageno = 3, 'Some Rooms',
    IF(packageno = 4, 'Other Rooms',
    IF(packageno = 5, 'Outdoor Rooms',
    IF(packageno = 6, 'Indoor Rooms',
    IF(packageno = 7, 'Commercial Rooms',
    IF(packageno = 8, 'Residential Rooms',
    IF(packageno = 9, 'Industrial Rooms',
    IF(packageno = 10, 'Kitchen Rooms',
    IF(packageno = 11, 'Office Rooms',
    IF(packageno = 12, 'Bedrooms', NULL))))))))))))

You could also create a PackageNumberDescription table and do the update from that like this:

PackageNo  PackageDescription
---------  ------------------
1          Interior Rooms
2          Exterior Rooms

UPDATE packages p JOIN PackageNumberDescription d on p.packageno = d.packageno SET p.packagedescription = d.packagedescription

Answer 2

If you have a small number of ids, you can do something like this:

UPDATE package SET description = 'Interior Rooms' WHERE packageNo = 1;
UPDATE package SET description = 'Exterior Rooms' WHERE packageNo = 2;

Might get a bit annoying if you have hundreds or thousands.

Or, instead of duplicating descriptions, you could have a seperate table, that has the descriptions and link to it.

PackageID    Description
---------    --------------
1            Interior Rooms
2            Exterior Rooms

Then you could change your selects to include the other table:

SELECT * FROM package p INNER JOIN packageRooms pr ON p.packageID = pr.packageID