How to keep nesting SQL statements?-CodePudding

I have the following relational model (primary keys between asterisks, and foreign keys preceded with a hash sign):

SALLE ( *nSALLE* , nameSALLE)
POSTE ( *nPOSTE* , #nSALLE)
LOGICIEL ( *nLOG* , purchaseDate)
INSTALLER ( #nPOSTE , #nLOG,...)

The question is to attribute a system date to the purchaseDate of LOGICIEL that are purchased before 2018 and that are installed in SALLE with the name "SALLE3"

I know that the idea is to keep joining tables, but I'm confused about how to nest the statements.

SQL> UPDATE LOGICIEL
  2  SET purchaseDate= GETDATE()
  3  WHERE EXTRACT(YEAR FROM purchaseDate) < 2018
  4  AND EXISTS(
  5  SELECT * FROM SALLE S
  6  INNER JOIN POSTE P ON S.nSALLE=P.nSALLE
  7  WHERE ...

CodePudding user response：

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.