How do I replace an entire column in MySQL?

Question

Suppose I have the following table (Table1) in MySQL:

FirstName Residence
Bob       USA
Jane      Canada
Steve     China

I also have the following table (Table2) in MySQL:

Residence
Japan
Canada
Mexico

I want to update Table1 so that the final result is:

FirstName Residence
Bob       Japan
Jane      Canada
Steve     Mexico

Right now, I'm thinking of doing the following: 1) make a primary key on both tables 2) drop the "Residence" column from Table1 3) do a join on the tables. Is there an easier, more efficient way to update the column? A join would take at least linear time, when this should be a constant time operation. In addition, using multiple update queries would be tedious and slow.

Inserting a row into a MySQL database seems fairly quick and straightforward. I'm wondering if there is a similarly easy way to insert a column into a MySQL database.

Thanks!

How do you know what people correspond to which residences on table2? Does table2 have any other columns? — Brian DeMilia
– Brian DeMilia, Commented Jul 25, 2014 at 1:01
For the sake of argument let's just say that the residences on table2 correspond directly to the people. Table2 does not have any other columns. — Rainmaker
– Rainmaker, Commented Jul 25, 2014 at 1:04
Alternatively, suppose I want to go to the Residence column, load it into Java, do some processing on it (e.g. strip whitespace, add capital city, capitalize all letters, etc), and then load it back into MySQL. I would rather not do the processing in SQL since I have to make extensive changes. — Rainmaker
– Rainmaker, Commented Jul 25, 2014 at 1:07
@Rainmaker . . . Tables in SQL (and MySQL) represent unordered sets. You need a column to specify the ordering -- an autoincremented id, for instance, or an insert datetime. — Gordon Linoff
– Gordon Linoff, Commented Jul 25, 2014 at 1:08
@GordonLinoff - you're right, I guess my initial example was not very good. But what about my previous comment - what if I want to reformat the column in Java and then reinsert it into MySQL? — Rainmaker
– Rainmaker, Commented Jul 25, 2014 at 1:10

Brian DeMilia · Accepted Answer · 2014-07-25 01:21:37Z

2

There are events that can change the order of rows in your tables, so you really want to have a column to distinguish the order.

If for the sake of argument you thought you could rely on whatever order in which the rows happen to be stored, you could use the following:

See fiddle at: http://sqlfiddle.com/#!2/01082b/1/0

update table1 t
join ( select firstname, residence, @rw := @rw + 1 as row_number
  from table1
 cross join (SELECT @rw := 0) r ) x on t.firstname = x.firstname
join ( select residence, @rx := @rx + 1 as row_number
  from table2
 cross join (SELECT @rx := 0) r ) y on y.row_number = x.row_number
set t.residence = y.residence

answered Jul 25, 2014 at 1:21

Brian DeMilia

13.3k1 gold badge25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jacky Cheng · Accepted Answer · 2014-07-25 04:20:02Z

I am sorry if I misunderstood, but doesn't a simple update sqll could fix all these??

According to OP's comment:

suppose I want to go to the Residence column, load it into Java, do some processing on it (e.g. strip whitespace, add capital city, capitalize all letters, etc), and then load it back into MySQL. I would rather not do the processing in SQL since I have to make extensive changes

so would this help? Assuming you'll have a considerable amount of entries, here are some fake code to show my concept.

resultset = db.query("select FirstName, Residence from table");
hashmap = process(resultset);
String createSql = "create table temp (firstname, residence)";
db.execute(createSql);    
StringBuffer sb = "insert into temp values ";
for (int i=0; i<hashmap.size;i++){
    //hashmap needs to be loop with iterator, this is just me being lazy
    sb.append("("+hashmap(firstname)+", "+hashmap(residence)+")");
}
db.execute(sb.toString());
db.execute("update table inner join temp on table.firstname=temp.firstname set table.residence = temp.residence")
//remove temp after this maybe

Collectives™ on Stack Overflow

How do I replace an entire column in MySQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related