MySQL update with select from another table

Question

I've got 3 tables. Companies, Kommuner and Fylker.

The companies table have an empty field forretningsadresse_fylke but an other field forretningsadresse_kommune with a value.

So basically, I need to fill in forretningsadresse_fylke, based on the value of forretningsadresse_kommune.

Now, the value of forretningsadresse_kommune and the value I want for forretningsadresse_fylke is stored in the Kommuner and Fylker tables.

So I wrote this query, but that doesn't seem to work because after 600 seconds the "MySQL server goes away".

UPDATE companies, fylker, kommuner
SET companies.forretningsadresse_fylke = (
    SELECT fylkeNavn 
    FROM fylker 
    WHERE fylker.fylkeID = kommuner.fylkeID
)
WHERE companies.forretningsadresse_kommune = kommuner.kommuneNavn

Here is what the Kommuner and Fylker tables look like.

Kommuner Table

Fylker Table

Companies table

companies Table

            | forretningsadresse_fylke  | forretningsadresse_kommune |
            |===========================|============================|
            |                           |                            |
            |                           |                            |
            |                           |                            |
            |                           |                            |
            |                           |                            |
            |                           |                            |

So I was wondering if there was something wrong with the query? Also, it might be good to mention, the table I try to update (Companies) has over 1 million rows.

Thanks in advance!

can you describe your tables? or at lest `Companies` table – Wajih Apr 30 '16 at 14:04 — Wajih, Apr 30 '16 at 14:04
I edited the post. Now you can see the companies table too. – Kaizokupuffball Apr 30 '16 at 14:12 — Kaizokupuffball, Apr 30 '16 at 14:12

score 10 · Accepted Answer · answered Apr 30 '16 at 14:28

You do not want fylker in the UPDATE statement. You should also be using a proper join. So the first rewrite is:

UPDATE companies c JOIN
       kommuner k
       ON c.forretningsadresse_kommune = k.kommuneNavn
    SET c.forretningsadresse_fylke = (SELECT f.fylkeNavn 
                                      FROM fylker f
                                      WHERE f.fylkeID = k.fylkeID
                                     );

If we assume a single match in fylker, then this is fine. If there are multiple matches, then you need to choose one. A simple method is:

UPDATE companies c JOIN
       kommuner k
       ON c.forretningsadresse_kommune = k.kommuneNavn
    SET c.forretningsadresse_fylke = (SELECT f.fylkeNavn 
                                      FROM fylker f
                                      WHERE f.fylkeID = k.fylkeID
                                      LIMIT 1
                                     );

Note: This will update all companies that have a matching "kommuner". If there is no matching "fylker" the value will be set to NULL. I believe this is the intent of your question.

Also, table aliases make the query easier to write and to read.

Yea, thanks! The last query you built works great! I was not sure I could use JOIN statements inside an UPDATE, I though that was only for SELECT. But know I know. I lear something new everyday! Thank you! :) — Kaizokupuffball, Apr 30 '16 at 16:15

score 2 · Answer 2 · edited May 23 '17 at 10:28

2

you can refer this question

https://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query

    UPDATE companies c
    JOIN Kommuner k ON c.kommuneID = k.kommuneID
    JOIN fylker f ON f.fylkeID = k.fylkeID
    SET c.forretningsadresse_fylke = f.fylkeNavn

edited May 23 '17 at 10:28

Community

1
1

answered Apr 30 '16 at 14:19

keronconk

359
2
10

score 2 · Answer 3 · answered Apr 30 '16 at 14:20

2

Try this:

UPDATE companies c
SET companies.forretningsadresse_fylke = (
    SELECT fylkeNavn 
    FROM Commoner k Left join Fylker f ON f.fylkeID = k.fylkeID
    where k.kommuneNavn = c.forretningsadresse_kommune
)

answered Apr 30 '16 at 14:20

Wajih

4,227
2
25
40

score 0 · Answer 4 · edited May 23 '17 at 12:07

0

UPDATE companies
SET companies.forretningsadresse_fylke = fylker.fylkeNavn
FROM companies, fylker, kommuner
WHERE companies.forretningsadresse_kommune = kommuner.kommuneNavn AND fylker.fylkeID = kommuner.fylkeID

https://stackoverflow.com/a/1068471/3866134

edited May 23 '17 at 12:07

Community

1
1

answered Apr 30 '16 at 14:19

Jose Hermosilla Rodrigo

3,513
6
22
38

MySQL update with select from another table

4 Answers4

Linked