All geek questions in one place

MySQL Script to set new column values ​​based on a different column value

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

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

Etc ....

But from the very beginning I did not add a description. Instead, I added the package number.

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

I know a bit of MySQL, but I have never done anything like existing lines.

Any help would be appreciated!

+4
source share
3 answers

The update request will be

 UPDATE table_name SET packagedescription = 'Interior Rooms' WHERE packageno = 1 UPDATE table_name SET packagedescription = 'Exterior Rooms' WHERE packageno = 2

The same goes for others.

+1
source

You can also do this in one request:

 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 can also create the PackageNumberDescription table and perform the update as follows:

 PackageNo PackageDescription --------- ------------------ 1 Interior Rooms 2 Exterior Rooms UPDATE packages p JOIN PackageNumberDescription d on p.packageno = d.packageno SET p.packagedescription = d.packagedescription
+7
source

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

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

It may seem a little annoying if you have hundreds or thousands.

Or instead of duplicating descriptions, you can have a separate table in which there are descriptions and links to it.

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

Then you can change your settings to include another table:

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

Source: https://habr.com/ru/post/1487678/

More articles:


All Articles