sqlsql-serverinsertinsert-into

How to insert a value for specific rows which need to be joined


So I want to insert my 'GarageID' from my newly made and filled 'Garage' table into my already existing 'Auto' (Dutch word for car) table in the column 'GarageID'. So far so good. Where I get stuck however is that I want to insert my 'GarageID' for specific cars. So for example I want 'GarageID = 3' for all the Renault Clio cars. I already would've gotten stuck at that point if the types of cars existed in the same table. But no, they were splitted and so the car models/types are to be found in another table -> AutoModelID (Car model ID, but again Dutch words for them).

I've already tried several things. But my mind got kind of in a spiral. I feel like I at least should use the 'WHERE' and the 'INNER JOIN' statements. However I don't know how to do it. I've singled out the values, but I have trouble seeing how I can use the 'WHERE' statement and 'INNER JOIN' statement if I'm stuck in the Garage table.

If my explanation wasn't clear enough (I won't blame you), here is a model of my database. database model


Solution

  • You can try an UPDATE inner joining auto and automodel. You than had acces to the columns from automodel in a WHERE clause.

    Example for setting the garage ID 3 for all Renault Clios:

    UDAPTE a
           SET a.garageid = 3
           FROM auto a
                INNER JOIN automodel am
                           ON am.id = a.automdelid
           WHERE am.merk = 'Renault'
                 AND am.model = 'Clio';