CODEWITHSUNDEEP
mysql
Frd
Frd

Reputation: 3

How to copy another table's record

I have table x, y

Table x

Id       name.       wage    
1.       A.          10
2.       B.          12
3.       C.          13

Table y

Id.      Bonus    
1.       5
3.       6

I want to create another table but with the records of table x but in wage i want to add it with bonus so the output like this

Id       name.       wagebonus    
1.       A.          15    
2.       B.          12    
3.       C.          19

I tried 

insert into newtable 
select id, name, wage+bonus from x, y

But failed

Upvotes: 0

Views: 39

Answers (1)

Nick
Nick

Reputation: 147166

If you want to create a new table, you need a CREATE TABLE command, not just an INSERT. The other problem with your query is that it doesn't take account of the fact that a person may not get a bonus, so you need to use a LEFT JOIN instead of an INNER JOIN to ensure all rows from x end up in the new table. This will do what you want:

CREATE TABLE newtable AS
SELECT x.Id, x.name, x.wage + COALESCE(y.bonus, 0) AS wagebonus
FROM x
LEFT JOIN y ON y.Id = x.Id;
SELECT * FROM newtable ORDER BY Id;

Output:

Id  name    wagebonus
1   A       15
2   B       12
3   C       19

Note that creating a new table for this is probably overkill. You could do the same with a VIEW, including adding an ORDER BY clause if required:

CREATE VIEW newview AS
SELECT x.Id, x.name, x.wage + COALESCE(y.bonus, 0) AS wagebonus
FROM x
LEFT JOIN y ON y.Id = x.Id
ORDER BY Id;
SELECT * FROM newview

Output is the same as above.

Demo on dbfiddle

Upvotes: 1

Related Questions