0

I have 3 tables, "cars", "elements" and "element_status" (for example). I'm NOT able to modify the SQL structure. Here's the code:

CREATE TABLE cars(
    idCar VARCHAR(60) NOT NULL,

    CONSTRAINT PK_cars_idCar PRIMARY KEY (idCar));


CREATE TABLE elements(
    idElement VARCHAR(30) NOT NULL,
    idCar VARCHAR(60) NOT NULL,

    CONSTRAINT FK_elements_idCar FOREIGN KEY (idCar) REFERENCES cars(idCar),
    CONSTRAINT PK_elements_idElement_idCar PRIMARY KEY (idElement, idCar));


CREATE TABLE element_status(
    idElement VARCHAR(30) NOT NULL,
    status BOOLEAN NOT NULL,

    CONSTRAINT FK_status_idelement FOREIGN KEY (idElement) REFERENCES elements(idelement),
    CONSTRAINT PK_status_idElement PRIMARY KEY (idElement));

So, here's the plot. Each element has a row in element_status. Each element_status row references one element and that element references one car.

So I have a row in "element_status" with idElement="engine" and status=FALSE. This engine references an engine in "elements" with, let's say, idCar="car_1234". But there a lot of engines there, each one pointing to it's car.

I need to update the status to TRUE in "element_status" of the engine with idCar="car_1234" and I need help with it. My mind says it should be easy, (subquery I guess) but actually i don't know how to do it.

Thanks!!

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Alv M
  • 1
  • 3
  • No subquery required. Just a join the tables and use a `WHERE` clause that matches the specific car you want. There are many SO questions that show how to do an update with a join. – Barmar Sep 21 '16 at 18:28
  • Thanks!! Always so helpful (: – Alv M Sep 23 '16 at 15:35
  • Anyway, if someone could put the code I'll be really grateful cause I'm a newbie in SQL. – Alv M Sep 23 '16 at 16:51
  • Answers can't be posted to closed questions. – Barmar Sep 23 '16 at 16:53
  • I solved it this way: UPDATE element_status SET status=1 WHERE idElement= (SELECT idElementFROM elements WHERE idCar='thecaricant' AND idElement='lementiwant'); Thank you all – Alv M Oct 03 '16 at 16:06
  • That will work when there's just one `idElement` being selected. The more general solution is to use a `JOIN`, as in the duplicate question. – Barmar Oct 04 '16 at 19:29

0 Answers0