How to keep nesting SQL statements?
Solution 1:
You are very close. Just join installer, poste and salle on their keys. You can use EXISTS
or IN
for this.
UPDATE logiciel
SET purchaseDate = SYSDATE
WHERE purchaseDate < DATE '2018-01-01'
AND nlog IN
(
SELECT i.nlog
FROM installer i
JOIN poste p ON p.nposte = i.nposte
JOIN salle s ON s.nsalle = p.nsalle
WHERE s.namesalle = 'SALLE3'
);
The same is possible with a chain of EXISTS
or IN
:
AND nlog IN
(
SELECT i.nlog
FROM installer i
WHERE i.nposte IN
(
SELECT p.nposte
FROM poste p
WHERE p.nsalle =
(
SELECT s.nsalle
FROM salle s
WHERE s.namesalle = 'SALLE3'
)
)
);
In the end this is a matter of personal preference.