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
  • 499,602
  • 82
  • 638
  • 795
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
  • SO is not a free coding service. Newbie or not, you're expected to do your own work. Try to write the code. If you can't get it working, post what you tried, and I'll reopen the question so we can show you how to correct it. But if you can't generalize from examples like in the other question, you're in the wrong line of work. You can't expect everyone to fill in for you. – Barmar Sep 23 '16 at 16:55
  • 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