SQL: Adding a column with data to an existing SQL table

Question

I have a table (tableA):

Col1	Col2
abc	123
def	456
ghi	789

to which I want to add Col3 with entries S, M, L such that (tableB)

Col1	Col2	Col3
abc	123	S
abc	123	M
abc	123	L
def	456	S
def	456	M
def	456	L
ghi	789	S
ghi	789	M
ghi	789	L

I only know of a way to add a column with single values (i.e. by using the ALTER command with default) but not with a column multiple data points.

You may add a column with no data and then insert all the source records joined with list of values for new column — astentx
– astentx, Commented Jul 17, 2021 at 22:08
Probably easiest to insert rows into a new table to replace the original using a cross join to your col3 data. — Stu
– Stu, Commented Jul 17, 2021 at 22:10

sticky bit · Accepted Answer · 2021-07-17 22:31:27Z

1

Add the column

ALTER TABLE tablea
            ADD COLUMN col3 varchar(1);

and set the value for the existing rows to 'S'.

UPDATE tablea
       SET col3 = 'S';

Then use an INSERT ... SELECT ... from the table cross joined with 'M's and 'L's into the the table to insert the missing rows.

INSERT INTO table3
            (col1,
             col2,
             col3)
            SELECT t.col1,
                   t.col2,
                   v.col3
                   FROM tablea t
                        CROSS JOIN (VALUES ('M'),
                                           ('L')) v
                                                  (col3);

edited Jul 17, 2021 at 22:31

answered Jul 17, 2021 at 22:13

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

labrynth Over a year ago

Thank you this works. This would also work: select a.* ,c.col3 from tableA a cross join (values ('M'),('L'),('S')) c (col3)

sticky bit Over a year ago

@labrynth: "This would also work: ..." -- It would generate such a set, yes. But it wouldn't materialize it in the table as you have asked for.

Collectives™ on Stack Overflow

SQL: Adding a column with data to an existing SQL table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Col1	Col2	Col3
abc	123	S
abc	123	M
abc	123	L
def	456	S
def	456	M
def	456	L
ghi	789	S
ghi	789	M
ghi	789	L

Col1	Col2	Col3
abc	123	S
abc	123	M
abc	123	L
def	456	S
def	456	M
def	456	L
ghi	789	S
ghi	789	M
ghi	789	L

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

Col1	Col2	Col3
abc	123	S
abc	123	M
abc	123	L
def	456	S
def	456	M
def	456	L
ghi	789	S
ghi	789	M
ghi	789	L