Comment remplacer une sous-requête dans une clause where?

Voici l'idée générale (pour les identifiants explicites):

        UPDATE store_status dest
        SET start_date = src.start_dt,
            end_date = src.end_dt,
            last_update_date = getDate()
        FROM stores src
        WHERE dest.store_id = src.store_id 
                AND dest.year_id = src.year_id
               AND store_type_id IN (5,6,7,8,9,10)
go

UPDATE store_doc dest
SET approve_start_date = src.start_dt,
    approve_end_date = src.end_dt,
    status_id = max_status_id
FROM (SELECT max(status_id) max_status_id, max(start_date) start_dt,
         max(end_date) end_dt, store_id, year_id FROM store_status 
      GROUP BY store_id, year_id) src
WHERE dest.store_id = src.store_id 
    AND dest.year_id = src.year_id

go

Parcourir les magasins:

declare 
  @storeID int, @yearID int
begin
  set @storeID = 1
  set @yearID = 2019

        UPDATE store_status dest
        SET start_date = src.start_dt,
            end_date = src.end_dt,
            last_update_date = getDate()
        FROM stores src
        WHERE dest.store_id = src.store_id 
                AND dest.year_id = src.year_id
                AND src.store_id=@storeID
                AND src.year_id=@yearID

end
go

Je n'ai pas vu ColdFusion depuis un moment ... cette balise CFQUERY me rappelle de bons souvenirs :)

Si vous exécutez la même requête UPDATE pour un groupe d'enregistrements, vous devez exécuter une seule requête par lots (comme vous l'avez suggéré). Pour ce faire pour tous les magasins, vous pouvez utiliser une variante de votre requête findStores comme source de vos UPDATE , quelque chose comme ceci:

<!--- Update status --->
<cfquery name="updateStatus" datasource="test">
  UPDATE store_status tgt
  FROM (
    -- findStores query
    SELECT store_id, year_id, start_dt, end_dt 
    FROM stores 
    WHERE store_type_id IN (5,6,7,8,9,10)
  ) src
  SET start_date = src.start_dt,
    end_date = src.end_dt,
    last_update_date = getDate()
  WHERE status_id = (
    SELECT status_id 
    FROM store_status 
    WHERE store_id = src.store_id 
    AND year_id = src.year_id
  )
</cfquery>

<!--- Update store info --->
<cfquery name="updateStoreDoc" datasource="test">
  UPDATE store_doc tgt
  FROM (
    -- findStores query with MAX(status) calculation
    SELECT 
      st.store_id, st.year_id, st.start_dt, st.end_dt, 
      MAX(stt.status_id) AS status_id_max
    FROM stores st
    LEFT JOIN store_status stt ON st.store_id = stt.store_id -- Get status info
      AND st.year_id = stt.year_id
    WHERE st.store_type_id IN (5,6,7,8,9,10)
    GROUP BY 1,2,3,4
  ) src
  SET approve_start_date = src.start_dt,
    approve_end_date = src.end_dt,
    status_id = src.status_id_max
  WHERE store_id = src.store_id
  AND year_id = src.year_id
</cfquery>

Je ne sais pas si votre SGBD sous-jacent prend en charge cette syntaxe et comment Cold Fusion la gérera, il faudra donc peut-être quelques bricolages pour le faire fonctionner.

De plus, qu'essayez-vous de faire exactement dans la requête updateStatus ? Vous faites référence à status_id dans votre clause WHERE , mais pas à store_id et year_id . En revanche, la requête updateStoreDoc fait référence à ces deux champs dans la clause WHERE . Je ne sais pas si la requête ci-dessus fonctionnera correctement pour updateStatus sous cette forme sans faire référence au PK de la table.

Et une dernière chose à considérer est que le store_status La table est mise à jour dans la première requête par lots, puis référencée dans la deuxième requête par lots. Vous devrez peut-être changer l'ordre de ces requêtes en fonction du résultat final que vous attendez.

Je ne connais pas Sybase. Sur la base de mon expérience avec d'autres systèmes de base de données, la façon dont vos requêtes sont configurées, même si vous utilisiez une table temporaire, vous devrez soit boucler à l'aide d'un curseur, soit effectuer une jointure multi-colonnes sur la table temporaire. Aucun des deux n'apporte un avantage dans la grande majorité des cas.

Dans cet esprit, à moins qu'il y ait un problème de performances nécessitant une solution de contournement de table temporaire, vous pouvez simplement faire la jointure sur les tables d'origine comme ci-dessous. Je n'ai pas testé cela, vous devriez donc confirmer la logique avant de mettre en service.

<cfquery datasource="test">
    update store_status
    set start_date = stores.start_date
        , end_date = stores.end_date
        , last_update_date = getDate()
    from store_status
        inner join stores on store_status.store_id = stores.store_id
            and store_status.year_id = stores.year_id
    WHERE stores.store_id = <cfqueryparam value="#storeID#" />
        and stores.year_id = <cfqueryparam value="#yearID#" />
        and stores.store_type_id IN (5,6,7,8,9,10);

    update store_doc
    set approve_start_date = stores.start_date
        , approve_end_date = stores.end_date
        , status_id = (SELECT max(status_id) FROM store_doc WHERE store_id = stores.store_id AND year_id = stores.year_id)
    from store_doc
        inner join stores on store_doc.store_id = stores.store_id
            and store_doc.year_id = stores.year_id
    WHERE stores.store_id = <cfqueryparam value="#storeID#" />
        and stores.year_id = <cfqueryparam value="#yearID#" />
        and stores.store_type_id IN (5,6,7,8,9,10)
</cfquery>