CODEWITHSUNDEEP
sqlpostgresql
labrynth
labrynth

Reputation: 155

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

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.

Upvotes: 0

Views: 61

Answers (1)

sticky bit
sticky bit

Reputation: 37472

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);

Upvotes: 1

Related Questions