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-26T14:27:12.383

178

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

edited Mar 26 '17 at 14:27

answered Sep 12 '12 at 18:58

Tom

6,593
3
21
42

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 Jun 20 '20 at 07:49
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 Jun 20 '20 at 07:54
Is there any way to do this without disabling safe mode in mysql. – jnrdn0011 Jun 20 '20 at 07:57
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 – Sudheer Kumar Nov 10 '21 at 10:23

score 26 · Answer 2 · answered Oct 13 '13 at 16:17

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

score 7 · Answer 3 · edited Oct 08 '15 at 09:00

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 08 '15 at 09:00

Pang

9,564
146
81
122

answered May 26 '15 at 06:25

Mohammad Imran

125
1
6

Could you please elaborate more your answer adding a little more description about the solution you provide? – abarisone May 26 '15 at 06:29
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.. :) – Mohammad Imran Jul 24 '15 at 06:24

score 5 · Answer 4 · edited Jan 02 '17 at 11:29

5

I think this should work

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

edited Jan 02 '17 at 11:29

Faisal

4,591
3
40
49

answered Sep 12 '12 at 18:57

Gonzalo.-

12,512
5
50
82

score 4 · Answer 5 · answered Jul 20 '20 at 13:08

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";

Faisal · Answer 6 · 2017-01-02T11:08:22.007

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 02 '17 at 11:08

answered Jan 02 '17 at 10:59

Faisal

4,591
3
40
49

score 1 · Answer 7 · answered Nov 30 '17 at 14:49

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 '17 at 14:49

Abadis

2,671
5
28
42

MySQL update table based on another tables value

7 Answers7

Linked

Related