Mettre à jour la requête avec des jointures et la clause Group by

Question

J'ai la requête suivante et j'essaie de mettre à jour table1 avec le montant total. Y a-t-il un moyen de le faire en une seule étape ?

select e.id
     , p.id
     , case  
         when count(distinct e.item) = 1 then 100
         when count(distinct e.item) = 2 then 150
         when count(distinct e.item) = 3 then 200
         when count(distinct e.item) = 4 then 225
         when count(distinct e.item) = 5 then 275
         when count(distinct e.item) = 6 then 325
         when count(distinct e.item) = 7 then 375
         when count(distinct e.item) = 8 then 450
         when count(distinct e.item) = 8 then 470
       end as TotalPay
  from table1 p 
  join table2 e on e.id = '111111'
               and p.id=e.itemid
group by e.id, p.id

Answer 1

Utilisation :

UPDATE TABLE1
   SET total = (SELECT CASE
                         WHEN COUNT(DISTINCT t2.item) = 1 THEN 100
                         WHEN COUNT(DISTINCT t2.item) = 2 THEN 150
                         WHEN COUNT(DISTINCT t2.item) = 3 THEN 200
                         WHEN COUNT(DISTINCT t2.item) = 4 THEN 225
                         WHEN COUNT(DISTINCT t2.item) = 5 THEN 275
                         WHEN COUNT(DISTINCT t2.item) = 6 THEN 325
                         WHEN COUNT(DISTINCT t2.item) = 7 THEN 375
                         WHEN COUNT(DISTINCT t2.item) = 8 THEN 450
                         WHEN COUNT(DISTINCT t2.item) = 9 THEN 470
                       END
                  FROM TABLE2 t2
                 WHERE t2.itemid = id
                   AND t2.id = '111111'
              GROUP BY t2.id, t2.itemid)
 WHERE EXISTS(SELECT NULL
                FROM TABLE2 t
               WHERE t.itemid = id
                 AND t.id = '111111')

• La clause WHERE est nécessaire, sinon toutes les lignes de TABLE1 seront traitées. Ceux qui n'ont pas de lignes liées à TABLE2 auraient été mises à jour en NULL
• Oracle (IME, jusqu'à 10g) ne prend pas en charge les JOINTURES dans une clause UPDATE comme MySQL & SQL Server -- vous devez utiliser une sous-requête (corrélationnée dans cet exemple). Il ne vous permet pas non plus de définir un alias de table pour la table mise à jour, donc lorsque l'alias de table est omis comme vous le voyez dans l'exemple -- la colonne provient de la table sans alias (celle qui est mise à jour)

Answer 2

Essayez:

mise à jour table1 p 
set TotalPay = 
(
select case  
         when count(distinct e.item) = 1 then 100
         when count(distinct e.item) = 2 then 150
         when count(distinct e.item) = 3 then 200
         when count(distinct e.item) = 4 then 225
         when count(distinct e.item) = 5 then 275
         when count(distinct e.item) = 6 then 325
         when count(distinct e.item) = 7 then 375
         when count(distinct e.item) = 8 then 450
         when count(distinct e.item) = 8 then 470
       end as TotalPay
  from table2 e where p.id=e.itemid
                and e.id = '111111'  
)  

Comme cela a été souligné dans les commentaires, ce qui précède mettra à jour toutes les lignes de table1 même s'il n'y a pas de correspondance dans table2 - auquel cas il définira la colonne sur NULL. Pour éviter cela, ajoutez une clause WHERE - voir la réponse de OMGPonies.