batch updating of Database Table data in Mysql

Question

HiI have two tables.

tbl1

id             int(11)      NOT NULL

positionName   varchar(20)  NULL

positionId     int(11)      NULL

tbl2

positionId         int(11)      NOT NULL

positionName       varchar(20)  NULL

Originally, there is no column positionId in tbl1. Now will use positionId instead of positionName for tbl1.

The problem is there are over thousands of data already stored in both tables.

So, How can I successfully and quickly link to the correct positionId for the each data of tbl1 , in which positionName was previously used?

Is batch updating is a way?

Is there anyway that I can perform all things with SQL browser? If possible, I don't want to use coding.

Thanks ahead.

Mark Byers · Accepted Answer · 2011-10-12 07:12:33Z

1

Are you looking for a multiple-table UPDATE, like this?

UPDATE tbl1
JOIN tbl2 ON tbl1.positionName = tbl2.positionName
SET tbl1.positionId = tbl2.positionId
WHERE tbl1.positionId IS NULL

I'm assuming that positionName is unique in tbl2, but if it isn't, you'll need to consider how you wish to handle that.

answered Oct 12, 2011 at 7:12

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

1 Comment

NICE solution. Thanks you so much. works very well. Yes positionName is unique in tbl2