question

mysql_b_grader avatar image
mysql_b_grader 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?
updatemysqlcomputed-column
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thesuda avatar image
thesuda answered
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 :)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.