Question 269226 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – creating new table by conditioning on columns of existing table

I am a new sql learner. I am stuck with the following problem:

Suppose I have the following table :

ID detail
A1 11
A1 12
A1 null
A1 0
A2 11
A2 13

Now I want to create a new table with two columns ID and result (say), where each ID appears once. If any row corresponding to an ID has 12 in the detail column, then result = 1, else 0. So basically my result should be the following table:

ID detail
A1 1
A2 0

My attempt to solve this was:

insert into new_table select ID, (select detail,
                                     (case
                                        when detail = 12 then '0'
                                        else '1'
                                      end) as new_column from table) 
from table;

But this piece of code is giving me error : operand should contain 1 column. I do not know how to approach this problem. Please help me understand how to solve this. I am trying this in MySql.

Tags:

3

Answers


  1. 0

    Use a CTAS statement for the creating the table, and use a case expression with aggregation.

    create table table2 as (
  select id, 
   max(case when detail = 12 then 1 else 0 end) as detail
  from table1
  group by id
  );
    id detail
    A1 1
    A2 0
    Login or Signup to reply.
  2. 0

    You don’t need a subquery. You can make the expression directly in the select-list.

    Use GROUP BY to make the result have one row per value of ID.

    INSERT INTO new_table 
SELECT ID, MAX(CASE detail WHEN 12 THEN 1 ELSE 0 END)
FROM old_table
GROUP BY ID;
    Login or Signup to reply.
  3. 0

    EXISTS will return only the correct scalar value and it will be fast

    CREATE TABLE old_table
    (`ID` varchar(2), `detail` varchar(4))
;
    
INSERT INTO old_table
    (`ID`, `detail`)
VALUES
    ('A1', '11'),
    ('A1', '12'),
    ('A1', NULL),
    ('A1', '0'),
    ('A2', '11'),
    ('A2', '13')
;


    Records: 6  Duplicates: 0  Warnings: 0

    CREATE TABLE new_table
    (`ID` varchar(2), `detail` varchar(4))
;

    INSERT INTO new_table
SELECT DISTINCT ID, (EXISTS(SELECT 1 FROM old_table o2 WHERE o2.`detail` = 12 AND o2.ID = o1.ID))
FROM old_table o1

    Records: 2  Duplicates: 0  Warnings: 0

    SELECT* FROM new_table
    ID detail
    A1 1
    A2 0

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search