CODEWITHSUNDEEP
mysqlsqlsql-update
JVT038
JVT038

Reputation: 11

MySQL update from 2 different tables


I have a table called auto with car data (auto means car from Dutch to English) that looks like this: 

+----------+---------------+------------------+------------------+------------------+--------------+----------+----------------+
| Kenteken | Merk          | Type             | Aantal Cilinders | Chassis nummer   | Motor nummer | Eigenaar | Telefoonnummer |
+----------+---------------+------------------+------------------+------------------+--------------+----------+----------------+

'Eigenaar' means owner from Dutch to English.
There are no owners registered in this table.
The names of the owners are in another table called clients.
This is how the client or 'clienten' table looks like: 

+----------------------+------+--------------+----------------------+----------------------------+----------+----------------------------+
| Naam                 | ID   | t.a.v        | Voornaam             | Adres                      | Postcode | Woonplaats                 |
+----------------------+------+--------------+----------------------+----------------------------+----------+----------------------------+

In this table all the clients have an ID.
The clients are linked with their car in another table.
That table is called relation or 'relatie' in Dutch.
Here is how that table looks like: 

+----------+------+
| Kenteken | ID   |
+----------+------+

If the client with ID 1 has a car with license plate '123-1230' for example, then the table looks like this: 

+----------+------+
| Kenteken | ID   |
+----------+------+
| 123-1230 |    1 |

Kenteken means license plate in English.
My goal is to update all rows in auto.Eigenaar This is what I have so far:

UPDATE auto SET Eigenaar = 
    (SELECT Naam FROM clienten WHERE ID IN (
        SELECT ID FROM relatie LEFT JOIN auto ON relatie.Kenteken = auto.Kenteken
        )    
    ) WHERE Kenteken IN (
        SELECT Kenteken FROM relatie WHERE ID IN (
            SELECT ID FROM relatie LEFT JOIN auto ON relatie.Kenteken = auto.Kenteken
        )
        LIMIT 1
    )

I don't know what to do next.
Does anyone know how to do this?
With kind regards,

Upvotes: 0

Views: 39

Answers (1)

forpas
forpas

Reputation: 164099

You can do it by joining the 3 tables:

UPDATE auto a
INNER JOIN relatie r ON r.Kenteken = a.Kenteken
INNER JOIN clienten c ON c.ID = r.ID
SET a.Eigenaar = c.Naam

First you join the table auto to the table relatie on the columns Kenteken and then the table relatie to the table clienten on the columns ID and you get the name of the client from the column Naam.
Note that storing the name of the client/owner in the table auto is not the recommended way for relational databases. What you should store is the ID just like you do in the table relatie.

Upvotes: 1

Related Questions