CODEWITHSUNDEEP
mysqlsqlpostgresql
Kyap
Kyap

Reputation: 91

Insert values from one column to another in the same table

How can I insert values from one column to another in the same table?

Say I have:

Date    |    Suburb    |    Suburb2
010617       Sydney        Parammatta 
010718       Melbourne     St Kilda

I require:

Date    |    Suburb    |    Suburb2
010617       Sydney        Parammatta  #copy date and suburb2
010718       Melbourne     St Kilda    #copy date and suburb2
010617       Parammatta                #insert
010718       St Kilda                  #insert

In other words, I want to copy 'Suburb2 and 'Date' and insert them as new rows in 'Date' and 'Suburb' respectively.

I know I can achieve this by first copying into temporary column/s and cleaning up later but was wondering if there was a way to do this in one query?

I tried

INSERT INTO table (date, suburb) SELECT (date, suburb2);

but got a column "date" does not exist error.

Upvotes: 2

Views: 7149

Answers (2)

Kyap
Kyap

Reputation: 91

Looks like it was just my syntax that was off. This worked:

INSERT INTO table (date, suburb) SELECT date, suburb2 FROM table;

Upvotes: 1

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

Try:

insert into table(date,suburb) select date,suburb2 from table where suburb2 in ('Parammatta','St Kilda');

Note: The where condition can be modified accordingly and can be set on suburb, date or suburb2.

Upvotes: 2

Related Questions