CODEWITHSUNDEEP
mysql
jace8615
jace8615

Reputation: 25

populate table with values from master table

I have the following 2 tables: 

*newbatting*    *newmaster*
playerID        playerID
playerName      playerName
yearID
h
ab
avgBat

The table "newbatting" has multiple rows for the same playerID but no name attached to it. I need to be able to populate every row with the appropriate name corresponding to the playerID. Is there a chance anyone could explain to me how copying the playerName from one table to the other works? Any explanation would be greatly appreciated it. Thanks.

Upvotes: 0

Views: 64

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

First, you really shouldn't do this. The relationship between the tables on PlayerId is sufficient. You can use a join to get the name when you query:

select nb.*, m.playername
from newbatting nb join
     newmaster m
     on nb.playerid = m.playerid;

If for some inexplicable reason, you really want to have this redundant data in the newbatting table, you can do something similar:

update newbatting nb join
       newmaster m
       on nb.playerid = m.playerid
     nb.playername = m.playername;

Upvotes: 1

Jaylen
Jaylen

Reputation: 40381

If you want to populate the palyerName column then this is what you need

UPDATE newbatting AS n
INNER JOIN newmaster AS m ON m.playerID = n.playerID
SET n.playerName = m.playerName;

and if you are looking to get a dataset 

SELECT n.playerID, n.payname FROM newbatting AS n
INNER JOIN newmaster AS m ON m.playerID = n.playerID

Upvotes: 1

tronmcp
tronmcp

Reputation: 346

You can accomplish this with an UPDATE statement:

UPDATE newbatting nb, newmaster nm SET nb.playerName = nm.playerName WHERE nb.playerID = nm.playerID

This will relate the newbatting table to the newmaster table via the playerID

For every match in the newmaster table will bring over the playerName

when you alias the tables with the short name nb and nm you can easily see where the data is coming from and going to and this keeps the fields non-ambiguous so the database knows what you are updating and what fields you are talking about.

Upvotes: 0

Avinash Babu
Avinash Babu

Reputation: 6252

You can do this with some INSERT and SELECT statements.

It would be like 

ÌNSERT INTO table_one (column1) SELECT column2 FROM table_two

See the reference

Upvotes: 0

Related Questions