where condition in merge condition

Question

where condition in merge condition

Shambhu Rai 1,411

Hi Expert,

how to add where condition in merge condition in databricks below

where at. Salesman2 = 'CVCLBS'

merge into Authority at using customer c

on (at.CustomerId = c.CustomerId )

when (at.OpuCode='D' and at.issuerNumber=700669) or (at.OpuCode='A' and at.CustomerId = c.CustomerId) then 'KO' when LEN(TRIM (at.cardOffertype))=0 then 'ZZ' else at.cardoffertype end)

when matched then update

set cardoffertype=(case

when (at.OpuCode='D' and at.issuerNumber=700669) or (at.OpuCode='A' and at.CustomerId = c.CustomerId) then 'KO'

when LEN(TRIM(at.cardOffertype))=0 then 'ZZ'

else at.cardoffertype

end)

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Wilko van de Velde 2,241

Hi @Shambhu Rai ,

I think it would be something like:

MERGE INTO Authority as at USING customer as c
  ON (at.CustomerId = c.CustomerId )
  WHEN MATCHED AND at.Salesman2 = 'CVCLBS' THEN UPDATE SET 
   cardoffertype=(case
    when (at.OpuCode='D' and at.issuerNumber=700669) or (at.OpuCode='A') then 'KO'
    when LEN(TRIM(at.cardOffertype))=0 then 'ZZ'
    else at.cardoffertype 
end)

More info about merge:
https://docs.databricks.com/en/sql/language-manual/delta-merge-into.html

Kind Regards,

Wilko

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

Shambhu Rai 1,411 Reputation points

2023-12-08T07:51:38.37+00:00

Sir,

there is on more where condition,

antiauthority = select authority frm temptable)
Shambhu Rai 1,411 Reputation points

2023-12-11T03:38:48.8066667+00:00

suggestion please
Shambhu Rai 1,411 Reputation points

2023-12-11T03:42:31.8133333+00:00

suggestion pls
Wilko van de Velde 2,241 Reputation points

2023-12-11T06:57:18.7966667+00:00

Where should the extra condition be added?
Shambhu Rai 1,411 Reputation points

2023-12-11T14:42:51.4066667+00:00

HI Expert,

that is wanted to know where i can use this where condtion
Shambhu Rai 1,411 Reputation points

2023-12-11T23:15:17.83+00:00

suggestion pls
Wilko van de Velde 2,241 Reputation points

2023-12-12T07:35:07.4966667+00:00

There are several possibilities, but it all depends on your requirement of what should be filtered.
Harishga 6,005 Reputation points Microsoft External Staff

2023-12-13T09:58:05.87+00:00

Hi Shambhu Rai
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Shambhu Rai 1,411 Reputation points

2023-12-13T10:57:45.6+00:00

Hi,

i want to use in this condition but its not working

WHEN MATCHED AND at.Salesman2 = 'CVCLBS'

Wilko van de Velde 2,241

Dont know the exact column names, but it should be something like this:

MERGE INTO Authority as at USING customer as c
  ON (at.CustomerId = c.CustomerId )
  WHEN MATCHED AND at.Salesman2 = 'CVCLBS' AND at.authority in (select authority from temptable) THEN UPDATE SET 
   cardoffertype=(case
    when (at.OpuCode='D' and at.issuerNumber=700669) or (at.OpuCode='A') then 'KO'
    when LEN(TRIM(at.cardOffertype))=0 then 'ZZ'
    else at.cardoffertype 
end)

Share via

where condition in merge condition

0 additional answers

Your answer