CODEWITHSUNDEEP
mysqlsql
Sinmok
Sinmok

Reputation: 656

Copy values of one column to a new table

I'm trying to copy only a single column from a table to a newly created table.

Here is my SQL:

CREATE TABLE whatever(
            id INT NOT NULL AUTO_INCREMENT,
            PRIMARY KEY(id),
            uid INT,
            can_view TINYINT DEFAULT 0
        )

This works fine, however, I need to modify it so it populates the 'uid' column with the 'id' values from my users table.

Anyone have any idea how to go about this?

I tried

uid INT (SELECT id FROM users)

Which doesn't seem to work

Upvotes: 2

Views: 142

Answers (5)

Robin Castlin
Robin Castlin

Reputation: 10996

INSERT INTO whatever (uid)
SELECT id
FROM users

INSERT ... SELECT

Or as you might like it:

CREATE TABLE whatever (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    uid INT,
    can_view TINYINT DEFAULT 0
)
SELECT NULL AS id, id AS uid, 0 AS can_view
FROM users

CREATE TABLE ... SELECT

Upvotes: 4

king_nak
king_nak

Reputation: 11513

Create as Select:

CREATE TABLE whatever(
        id INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(id),
        uid INT,
        can_view TINYINT DEFAULT 0
    ) AS SELECT id AS uid FROM users;

Upvotes: 1

SALMAN
SALMAN

Reputation: 2031

Here is the simple answer and it will surely help you.

INSERT into whatever(uid) VALUES (SELECT id FROM users)

Thanks.

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

insert into whatever(uid ) values select id from users

Upvotes: 0

Randy
Randy

Reputation: 16677

after it is created - something like this:

INSERT into whatever( uid ) select id from users

Upvotes: 1

Related Questions