MYSQL Select Statment with Modification to tuple values

Question

Hi,

I have the following table:

bar_id, bar_name, subscription_id_fk, sub_name

eg:

1, Hard Rock, 2, Gold
2, TGIF, 1, Free

Now I need and SQL to extract this table, but where sub_name = Gold, I need to double the subscription_id_fk.

Can you please help ?

Edit: I need this value to be only changed in the result of the SQL statment

Answer 1

Maybe I don't understand the question, but this looks like a simple update to me:

UPDATE table 
SET subscription_id_fk = (subscription_id_fk * 2) 
WHERE sub_name = "Gold"

Answer 2

Are you looking for something as simple as

SELECT (subscription_id_fk * 2) as double_sif
FROM table
WHERE sub_name = 'Gold'

As you accepted this answer a few comments

• SELECT statements never change anything in the database, for that you need UPDATE, INSERT or DELETE (at least read wikipedia article on SQL)
• the name of the field suggests that it is a foreign key to another table and even though it is possible, normally multiplying keys don't make any sense

Answer 3

SELECT IF(sub_name = 'Gold', subscription_id_fk * 2, subscription_id_fk) as fk 
FROM my_table 

Answer 4

This may help you.

So, According to the link you try,
select subscription_id_fk * 2 from table where sub_name = 'Gold'

I hadn't try it practically.I just find it on internet. So, Try it.

Answer 5

Ok, if you need all rows, but only "Gold"-ones with doubled keys, you could use

SELECT 
    GREATEST(t.subscription_id_fk, IFNULL(t2.subscription_id_fk,0)) AS subscription_id_fk, 
    bar_id, 
    bar_name, 
    sub_name
FROM table AS t
LEFT JOIN (
    SELECT (subscription_id_fk * 2) AS subscription_id_fk, 
        id 
    FROM table t2 
    WHERE sub_name ="Gold") AS t2
ON (t.id = t2.id)