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

avatar image

mysql_b_grader
13 1 1 3

(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

avatar image

thesuda
102 2 3

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

avatar image

Magnus Ahlkvist
20.9k 19 39 42

(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

avatar image

Cyborg
10.8k 37 54 51

(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.

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:

x161
x100
x10

asked: Feb 15, 2011 at 08:00 PM

Seen: 7810 times

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

Copyright 2016 Redgate Software. Privacy Policy