Help in coalesce function in update statement

Question

Post reply

Help in coalesce function in update statement

vikramchander90

Ten Centuries

Points: 1093
More actions
April 17, 2020 at 5:50 pm
Go to Answer
#3743834

Can any1 help in update statement in coalesce statement
Begin tran
Declare @tran_hdr table
(
tran_no varchar(18)
)
Declare @tran_dtl1 table
(
tran_no varchar(18),
ref_no varchar(18),
ref_tamt numeric(28,2)
)
Declare @tran_dtl2 table
(
tran_no varchar(18),
ref_no varchar(18),
ref_tamt numeric(28,2)
)
Declare @error_table table
(
tran_no varchar(18),
ref_no varchar(18)
)
Insert into @tran_hdr values ('1')
Insert into @tran_hdr values ('2')
Insert into @tran_hdr values ('3')
Insert into @tran_dtl1 values ('1','D1',200)
Insert into @tran_dtl1 values ('1','D2',10)
Insert into @tran_dtl1 values ('1','D3',500)
Insert into @tran_dtl1 values ('2','D1',200)
Insert into @tran_dtl1 values ('2','D5',15)
Insert into @tran_dtl1 values ('2','D4',800)
Insert into @tran_dtl1 values ('3','D6',40)
Insert into @tran_dtl2 values ('1','T1',40)
Insert into @tran_dtl2 values ('1','T2',90)
Insert into @tran_dtl2 values ('1','T3',150)
Insert into @tran_dtl2 values ('2','T1',40)
Insert into @tran_dtl2 values ('2','T5',60)
Insert into @tran_dtl2 values ('2','T4',1000)
Insert into @tran_dtl2 values ('3','T6',20)
Insert into @error_table
Select tran_no, NULL
From @tran_hdr
Update tmp
Set ref_no = coalesce(tmp.ref_no +',','') + t1.ref_no
From @error_table tmp,
@tran_dtl1 t1
Where t1.tran_no = tmp.tran_no
And t1.ref_tamt > 100
Update tmp
Set ref_no = coalesce(tmp.ref_no +',','') + t2.ref_no
From @error_table tmp,
@tran_dtl2 t2
Where t2.tran_no = tmp.tran_no
And t2.ref_tamt < 100
And ISNULL(tmp.ref_no,'') = ''
Select *
From @error_table
Rollback tran

Need to get output as
1,d1,d3
2,d1,d4
3,6
Only 1st value is getting updated. Other values are not getting updated. Can any1 suggest solution / another approach.

Phil Parkin

SSC Guru

Points: 246965

April 17, 2020 at 7:32 pm

Answer

#3743869

WITH errs
AS (SELECT td.tran_no
          ,ref_no = STRING_AGG(td.ref_no, ',')
    FROM
    (
        SELECT t1.tran_no
              ,t1.ref_no
              ,t1.ref_tamt
        FROM @tran_dtl1 t1
        WHERE t1.ref_tamt > 100
        UNION ALL
        SELECT t2.tran_no
              ,t2.ref_no
              ,t2.ref_tamt
        FROM @tran_dtl2 t2
        WHERE t2.ref_tamt < 100
    ) td
    GROUP BY td.tran_no)
UPDATE et
SET et.ref_no = errs.ref_no
FROM @error_table et
    JOIN errs
        ON errs.tran_no = et.tran_no;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

I can get these results, can you explain what is wrong with them? I am trying to follow the conditions in your code, and this is what I get so far.

2020-04-17_14-13-58

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2160 More actions · Answer 2

Maybe STRING_AGG instead of COALESCE?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

vikramchander90 Ten Centuries Points: 1093 More actions · Answer 3

Can you please share the query used to get that ouput.. after the update statement getting output as

1 D1

2 D1

3 d6

vikramchander90 Ten Centuries Points: 1093 More actions · Answer 4

Ok will check... But it appears to be from 2017 and my server still has 2016 sql

vikramchander90 Ten Centuries Points: 1093 More actions · Answer 5

vikramchander90

Ten Centuries

Points: 1093

April 17, 2020 at 7:35 pm

#3743870

Thanks will check

vikramchander90 Ten Centuries Points: 1093 More actions · Answer 6

STRING_AGG is not supported in SQL 2016. Getting error STRING_AGG is not a built in function. Is there any other option for SQL 2016 please.

As when checked STING_AGG is supported from SQL 2017. Thanks for your help

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

The solution without STRING_AGG is much less elegant. Here is one way:

WITH ErrData1
AS (SELECT t1.tran_no,
           t1.ref_no,
           t1.ref_tamt
    FROM @tran_dtl1 t1
    WHERE t1.ref_tamt > 100
    UNION ALL
    SELECT t2.tran_no,
           t2.ref_no,
           t2.ref_tamt
    FROM @tran_dtl2 t2
    WHERE t2.ref_tamt < 100),
     ErrData2
AS (SELECT et.tran_no,
           ref.Ref_No
    FROM @error_table et
        CROSS APPLY
    (
        SELECT Ref_No = STUFF(
                        (
                            SELECT ', ' + CAST(ed.ref_no AS VARCHAR(MAX))
                            FROM ErrData1 ed
                            WHERE ed.tran_no = et.tran_no
                            FOR XML PATH('')
                        ),
                        1,
                        2,
                        ''
                             )
    ) ref )
UPDATE et
SET et.ref_no = errs.Ref_No
FROM @error_table et
    JOIN ErrData2 errs
        ON errs.tran_no = et.tran_no;

SELECT *
FROM @error_table;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2160 More actions · Answer 8

/* without STRING_AGG */
;with
ErrData1(tran_no, ref_no, ref_tamt) as (
    select t1.tran_no, t1.ref_no, t1.ref_tamt
    from @tran_dtl1 t1
    where t1.ref_tamt > 100
    union all
    select t2.tran_no, t2.ref_no, t2.ref_tamt
    from @tran_dtl2 t2
    where t2.ref_tamt < 100)
update et
set et.ref_no = ref.ref_no
from 
  @error_table et
 cross apply
  (select stuff((select ', ' + cast(ed.ref_no AS varchar(max))
                 from ErrData1 ed
                 where ed.tran_no = et.tran_no
                 for xml path('')), 1, 2, '') ref_no) ref;

/* with STRING_AGG */
;with
ErrData1(tran_no, ref_no, ref_tamt) as (
    select t1.tran_no, t1.ref_no, t1.ref_tamt
    from @tran_dtl1 t1
    where t1.ref_tamt > 100
    union all
    select t2.tran_no, t2.ref_no, t2.ref_tamt
    from @tran_dtl2 t2
    where t2.ref_tamt < 100)
update et
set et.ref_no = ref.ref_no
from 
  @error_table et
 cross apply
  (select string_agg(cast(ed.ref_no AS varchar(max)), ',') ref_no 
   from ErrData1 ed
   where ed.tran_no = et.tran_no) ref;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können