MySQL update table based on another tables value

Question

I have a two tables,

Here is my first table,

ID      SUBST_ID        CREATED_ID
1       031938          TEST123
2       930111          COOL123
3       000391          THIS109
4       039301          BRO1011
5       123456          COOL938
...     ...             ...

This is my second table,

ID      SERIAL_ID       BRANCH_ID
1       039301          NULL
2       000391          NULL
3       123456          NULL
...     ...             ...

I need to some how update all rows within my second table using data from my first table.

It would need to do this all in one update query.

Both SUBST_ID and SERIAL_ID match, it needs to grab the created_id from the first table and insert it into the second table.

So the second table would become the following,

ID      SERIAL_ID       BRANCH_ID
1       039301          BRO1011
2       000391          THIS109
3       123456          COOL938
...     ...             ...

Thank you for your help and guidance.

Tom · Accepted Answer · 2017-03-26 14:27:12Z

183

UPDATE TABLE2
       JOIN TABLE1
       ON TABLE2.SERIAL_ID = TABLE1.SUBST_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

edited Mar 26, 2017 at 14:27

answered Sep 12, 2012 at 18:58

Tom

6,6633 gold badges24 silver badges42 bronze badges

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

4 Comments

jnrdn0011 Over a year ago

Hi, I am getting an error while using this solution. 'Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.'

jnrdn0011 Over a year ago

I modified the query with where condition but still same error. QUERY: update contract c join contractstatus cs set c.status=cs.status where cs.contract=c.id;

jnrdn0011 Over a year ago

Is there any way to do this without disabling safe mode in mysql.

Sudheer Kumar Over a year ago

You need to define an Index for the columns that you need to update and then the error and then he safe mode error will be gone

RafaSashi · Accepted Answer · 2013-10-13 16:17:31Z

26

In addition to Tom's answer if you need to repeat the operation frequently and want to save time you can do:

UPDATE TABLE1
       JOIN TABLE2
       ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID
WHERE TABLE2.BRANCH_ID IS NULL

answered Oct 13, 2013 at 16:17

RafaSashi

17.3k8 gold badges90 silver badges99 bronze badges

Comments

Pang · Accepted Answer · 2015-10-08 09:00:59Z

7

UPDATE TABLE2
       JOIN TABLE1
       ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID 
WHERE TABLE2.BRANCH_ID IS NULL or TABLE2.BRANCH_ID='';

edited Oct 8, 2015 at 9:00

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered May 26, 2015 at 6:25

Mohammad Imran

1251 silver badge6 bronze badges

2 Comments

abarisone Over a year ago

Could you please elaborate more your answer adding a little more description about the solution you provide?

Mohammad Imran Over a year ago

by using join on subset_Id and Serial_Id you will get a Result set.and that result set will contain created id..now Concentrate on query after SET there we actually assigning or providing values from created_Id to Branch_Id ....hope you understand.. :)

Faisal · Accepted Answer · 2017-01-02 11:29:55Z

6

I think this should work

UPDATE secondTable
JOIN firsTable ON secondTable.SERIAL_ID = firsTable.SUBST_ID
SET BRANCH_ID = CREATED_ID

edited Jan 2, 2017 at 11:29

Faisal

4,7854 gold badges43 silver badges51 bronze badges

answered Sep 12, 2012 at 18:57

Gonzalo.-

12.7k5 gold badges53 silver badges84 bronze badges

Comments

vishwampandya · Accepted Answer · 2020-07-20 13:08:11Z

4

It is very simple to update using Inner join query in SQL .You can do it without using FROM clause. Here is an example :

    UPDATE customer_table c 

      INNER JOIN  
          employee_table e
          ON (c.city_id = e.city_id)  

    SET c.active = "Yes"

    WHERE c.city = "New york";

answered Jul 20, 2020 at 13:08

vishwampandya

1,20715 silver badges11 bronze badges

Comments

Faisal · Accepted Answer · 2017-01-02 11:08:22Z

1

Using INNER JOIN:

UPDATE TABLE1
INNER JOIN TABLE2 ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

Another alternative solution like below: Here I am using WHERE clause instead of JOIN

UPDATE 
    TABLE1,
    TABLE2
WHERE
    TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET 
    TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

edited Jan 2, 2017 at 11:08

answered Jan 2, 2017 at 10:59

Faisal

4,7854 gold badges43 silver badges51 bronze badges

Comments

Abadis · Accepted Answer · 2017-11-30 14:49:29Z

1

You can use this too:

update TABLE1 set BRANCH_ID = ( select BRANCH_ID from TABLE2 where TABLE1.SUBST_ID = TABLE2.SERIAL_ID)

but with my experience I can say that this way is so slow and not recommend it!

answered Nov 30, 2017 at 14:49

Abadis

2,8715 gold badges30 silver badges43 bronze badges

Collectives™ on Stack Overflow

MySQL update table based on another tables value

7 Answers 7

4 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related