x

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?
more ▼

asked Feb 15, 2011 at 08:00 PM in Default

mysql_b_grader gravatar image

mysql_b_grader
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
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 :)
more ▼

answered Feb 15, 2011 at 11:37 PM

thesuda gravatar image

thesuda
102 2

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
Feb 16, 2011 at 12:56 AM thesuda
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Feb 16, 2011 at 12:02 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
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 
more ▼

answered Feb 15, 2011 at 10:06 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x132
x68
x9

asked: Feb 15, 2011 at 08:00 PM

Seen: 5754 times

Last Updated: Feb 15, 2011 at 11:51 PM