Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register


2
(click on this box to dismiss)

How to combine two tables into one?

Suppose I have table users with cols user_id, username, pwd, and table user_status with cols user_id, is_logged. What is the best way to obtain a new table with cols user_id, username, pwd, is_logged with all my data in place?

link comment (0) accepted answer
asked 3 years ago
sqlcopter
284
87 Answers
2

See:

 1. CREATE TABLE LIKE syntax (http://www.cubrid.org/manual/840/en/CREATE%20TABLE%20LIKE)

 2. CREATE TABLE AS SELECT syntax (http://www.cubrid.org/manual/840/en/CREATE%20TABLE%20AS%20SELECT)

 3. INSERT SELECT syntax (http://www.cubrid.org/manual/840/en/INSERT%20%E2%80%A6%20SELECT%20Statement)

 4. UNION syntax http://www.cubrid.org/manual/840/en/Statement%20Set%20Operators

 

Basically, you can do something like this:

CREATE TABLE a (i INTEGER);
INSERT INTO a(i) VALUES(1),(2),(3);
CREATE TABLE b (i INTEGER);
INSERT INTO b(i) VALUES(4),(5),(6);
CREATE TABLE copy_a_b LIKE a;
-- insert everything from a and b:
INSERT INTO copy_a_b SELECT * FROM a UNION ALL SELECT * FROM b;
-- do not insert duplicates:
INSERT INTO copy_a_b SELECT * FROM a UNION SELECT * FROM b;

Or, for your specific question:
CREATE TABLE users(user_id CHAR(20), username VARCHAR(64), pwd VARCHAR(128)); 

CREATE TABLE user_status (user_id CHAR(20), is_logged INTEGER);

CREATE TABLE copy_user_staus AS 
	SELECT u.user_id user_id, u.username username, u.pwd pwd, us.is_logged is_logged
	FROM users u, user_status us
	WHERE u.user_id = us.user_id;


link comment (1)
answered 3 years ago
eusto
204
tagged




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: