asked
Setting value of a column based on criteria from another
Very basic MySQL user and trying to obtain information about support contracts. We have a column called products which lists all the products on an order (not separated out into individual rows) So order 1000 might have "products' = PROD1|PROD2|SUPPORT. What I want to do is create a new column and make it true/false and have a lookup or formula (i.e. If "products" contains "SUPPORT" or "PS12" or "PS24 then update "new_column" with "true" else default "false" Can this be done in MySQL?
UPDATE TableName SET FLAG = CASE WHEN PRODUCTS LIKE '%PROD1%' THEN 1 WHEN PRODUCTS LIKE '%PROD2%' THEN 1 WHEN PRODUCTS LIKE '%SUPPORT%' THEN 1 ELSE 0 END Should work :)
Note: My answer applies to SQL Server, not sure about MySql, apologies. :) You can see
http://dev.mysql.com/doc/refman/5.0/en/case-statement.html
Try case statement
UPDATE TableName
SET NewColumn = CASE products
WHEN 'SUPPORT' THEN 'TRUE'
WHEN 'PS12' THEN 'TRUE'
WHEN 'PS24 ' THEN 'TRUE'
ELSE 'FALSE'
END CASE
MySQL can probably be used for more or less the same things as most other RDBMS. I'm in no way an expert and this site is dedicated to answering questions about SQL Server. But since this is a not-so-complex query, I'll try anyway :) Why do you want to update a new column based on which products is in a column? If you want to use the new column to check which orders have a "SUPPORT" in them, the information is already on each row. Creating a new column is therefore not necessary. So I won't give an answer how to update the table, only how to query it to get that BIT a part of the result. This is T-SQL, but I think MySQL can handle variables as well DECLARE @product varchar(100) SELECT *, CASE WHEN PRODUCTS LIKE '%' + @product + '%' THEN 1 ELSE 0 END AS HasSpecificProduct FROM TableName Or if you mean you want to see if any of a number of hardcoded products are in the column, you could do: SELECT *, CASE WHEN PRODUCTS LIKE '%PROD1%' OR PRODUCTS LIKE '%PROD2%' OR PRODUCTS LIKE '%PROD3%' THEN 1 ELSE 0 END AS HasSpecificProduct FROM TableName
