Click here to Skip to main content
15,436,489 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Is it possible to enter a script in a table in the phpMyAdmin that will change the category name of certain entries which belong in a certain field? For example in the case below. How can I change all records with Product BMW X5 to category SUVs.
CustomerProduct Sector Category
BMW BMW X5 Vehicles Passenger Cars
BMW BMW X5 Vehicles Passenger Cars
BMW BMW X5 Vehicles Passenger Cars
BMW BMW X5 Vehicles Suvs

What I have tried:

This does not work SELECT (product like 'BMW X5' ? (category == Passenger cars : 'suvs') : product) as "product", category from table;
Posted
Updated 5-Jan-21 7:21am

Firstly, an SQL LIKE clause requires wildcards - without them it is identical to an equality test.

For SQL, '%' is a "match any number of characters" and '?' with match any single character: SQL Wildcard Characters[^]
So your LIKE query probably needs to be:
SQL
SELECT <columns list> FROM MyTable WHERE Products LIKE 'BMW X5%'


But that's not how you change values in SQL - for that, you use an UPDATE command:
SQL
UPDATE MyTable SET Category = 'SUVS' WHERE Products LIKE 'BMW X5%'

But even then, what you have is a very ... student-who-knows-no-better-ish ... data design.
You shouldn't store the same data in multiple rows: your Sector and Category columns should reference a Foreign Key in two other tables : Sectors and Categories so that the test string is stored in one place, and one place only. So when you move items from one Category to another, all you change is the ID and it now refers to a different row in teh Categories table, and thus a different category. You combine them using a JOIN if you need to get the vehicle together with the text category:
SQL
SELECT p.Product, c.Desc 
  FROM Mytable p
  JOIN Categories c ON p.CategoryID = c.ID
 
Share this answer
 
Quote:
Is it possible to enter a script in a table in the phpMyAdmin that will change the category name of certain entries which belong in a certain field? For example in the case below. How can I change all records with Product BMW X5 to category SUVs.

(...)

SQL
SELECT (product like 'BMW X5' ? (category == Passenger cars : 'suvs') : product) as "product", category from table


I'd strongly recommend to follow the instruction from OriginalGriff in solution #1.

In case when you want to temporary display text other than existing one, you can use CASE WHEN THEN END statement[^].

So... correct statement for MS SQL server is:
SQL
SELECT CASE WHEN product like 'BMW X5%' THEN 'suvs' ELSE product END as product
   , category
FROM tableA


If your database provider is differ than MS SQL Server, please find and read proper documentation.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900