user2212461 user2212461 - 5 months ago 7
SQL Question

How to set MYSQL column to certain value if a certain row exists?

I want to generate a new column in my table that is true if a row exists with certain conditions.

name | col1 | col2 | flag
--------------------------
a 1 2 0
a 2 3 0
b 1 2 0
b 4 3 0


Lets say I want to set the flag to 1 for every
name
identifier if a row exists with that
name
and where
col1=2
and
col2 = 3
. So this would result in:

name | col1 | col2 | flag
--------------------------
a 1 2 1
a 2 3 1
b 1 2 0
b 4 3 0


because for
a
a row with
col1=2
and
col2 = 3
exists, but for
b
, such a row doesn't exist.

In pseudocode I want something like this:

ALTER TABLE table_name
ADD flag TINYINT(1)
IF ##row with condition col1=value1 and col2=value2 exists#
GROUP BY name


How can I generate this column?

Answer

So you want just to get those values from db? or you want to add column? those are 2 different goals.

So if you need just to get those values you can:

http://sqlfiddle.com/#!9/65b4c2/1

SELECT t.*, t2.flag
FROM table_name t
LEFT JOIN (
  SELECT name, MAX(IF(col1=2 AND col2=3,1,0)) flag
  FROM table_name
  GROUP BY name
  ) t2
ON t.name = t2.name

and if you really need to add new column then you go this way:

http://sqlfiddle.com/#!9/226fb3/1

ALTER TABLE table_name ADD COLUMN flag TINYINT;

UPDATE table_name t
LEFT JOIN (
      SELECT name, MAX(IF(col1=2 AND col2=3,1,0)) flag
      FROM table_name
      GROUP BY name
      ) t2
ON t.name = t2.name
SET t.flag=t2.flag
Comments