Update multiple columns from select that have more columns

Question

I need to update 2 columns from a given query but the problem is that the query has more than 2 columns, so this is the correct syntax if I take off the additional columns :

UPDATE myTable TAO 
  SET (TAO.BASE_AMT,TAO.TAX_CD_VAT_PCT) = ( 
 SELECT SUM(decode(TAX_CD_VAT_PCT 
 , 0 
 , 0 
 ,d.monetary_amount)) 
 , MAX (D.TAX_CD_VAT_PCT) 
  FROM ps_VCHR_LINE_WTHD w 
  , PS_DISTRIB_LINE d 
 WHERE 1=1 
 -- some conditions
  GROUP BY d.business_unit, d.voucher_id 
  ORDER BY 1, 2)

The Original query is like that:

 SELECT d.business_unit, d.voucher_id,SUM(decode(TAX_CD_VAT_PCT 
 , 0 
 , 0 
 ,d.monetary_amount)) 
 , MAX (D.TAX_CD_VAT_PCT) 
  FROM ps_VCHR_LINE_WTHD w 
  , PS_DISTRIB_LINE d 
 WHERE 1=1 
 -- some conditions
  GROUP BY d.business_unit, d.voucher_id 
  ORDER BY 1, 2)

So if I remove d.business_unit, d.voucher_id will I have the same result? because I tried this in SQL developer when I remove it I don't get the same top rows.

Can someone Help?

Thanks.

There's all sorts of horribleness going on there! Are you really updating a table based on a query that returns multiple values, and expecting the `ORDER BY` clause to "sort this all out"? — Richard Hansell, Apr 16 '18 at 12:09
That traditional practices of not using joins is an issue definitely, Not ANSI-92 standard, use join instead of commas — Ven, Apr 16 '18 at 12:09
If you want the same result as before then you will probably need to change `ORDER BY 1, 2` to `ORDER BY d.business_unit, d.voucher_id`. But I would strongly recommend rethinking your approach here. — Richard Hansell, Apr 16 '18 at 12:10
give us sample data and expected result. if you remove the 2 columns you mentioned then the sum is different. — jose_bacoy, Apr 16 '18 at 12:10
your question ends in a very different way than it begins. what exactly are you asking for ? what is your aim ? select it or update it ? — Doruk, Apr 16 '18 at 12:14
@Doruk Yes you are right i ask if there is a better way than this if not will removing the additional columns resolve that or not because i don't get the same result when i tested it ? — kh.tab, Apr 16 '18 at 12:16
@RichardHansell I tried what you say and i got the same top rows will this resolve my problem ? Thank you — kh.tab, Apr 16 '18 at 12:16
i still don't understand. resolve what ? what is the problem ? — Doruk, Apr 16 '18 at 12:18
@Doruk if you look to the original query you will see that it's based on the first 2 columns `d.business_unit, d.voucher_id` but what i need to update is the last 2 columns so i have to delete the firts columns like in my first example , and when i put the query like that in SQLDevelopper i don't get the same top rows so i am confused if i have the good result or not — kh.tab, Apr 16 '18 at 12:22
How about doing the update from a subquery that has the group by? [Like in this older question](https://stackoverflow.com/questions/29692077/update-with-group-by) — LukStorms, Apr 16 '18 at 12:24
Changing the `ORDER BY` is simply making your update query the same as the original select query was. It should fix your problem, but it's not really a good solution. Using MERGE, sub-queries, common table expressions, even temporary tables would all be better solutions imho. — Richard Hansell, Apr 16 '18 at 13:07

score 0 · Answer 1 · answered Apr 16 '18 at 12:59

Use a MERGE statement. It is difficult to determine how you are correlating your update statement to the nested select (it doesn't seem that there is any correlation) but, assuming it is on the business_unit and voucher_id columns then something like:

MERGE INTO my_table dst
USING (
  SELECT d.business_unit,
         d.voucher_id,
         SUM( DECODE( TAX_CD_VAT_PCT, 0, 0, d.monetary_amount ) ) AS total_tax_cd_vat_pct,
         MAX (D.TAX_CD_VAT_PCT) AS max_tax_cd_vat_pct
  FROM   ps_VCHR_LINE_WTHD w 
         INNER JOIN
         PS_DISTRIB_LINE d 
         ON ( /* some conditions here */ )
  WHERE  1=1
         -- some conditions
  GROUP BY
         d.business_unit,
         d.voucher_id
) src
ON (
      src.business_unit = dst.business_unit
  AND src.voucher_id    = dst.voucher_id
)
WHEN MATCHED THEN
  UPDATE SET
   BASE_AMT       = src.total_tax_cd_vat_pct
   TAX_CD_VAT_PCT = src.max_tax_cd_vat_pct;

Update multiple columns from select that have more columns

1 Answers1