0

How to convert merge of SQL server to equivalent postgresql code: I am new to SQL Server and not aware how merge behaves

merge PortfolioAssetClassBenchmark_Subset as t
 using @inputBenchmark as s
    on t.PortfolioId = @PortfolioId and t.StartDate = s.F_STARTDATE
 when matched and isnull(s.F_BENCHMARK, 0) != 0 and t.AssetClassBenchmarkId != s.F_BENCHMARK then
    update set 
    t.AssetClassBenchmarkId = s.F_BENCHMARK
 when matched and isnull(s.F_BENCHMARK, 0) = 0 then
    delete  
 when not matched by target and isnull(s.F_BENCHMARK, 0) != 0 then
    insert (PortfolioId, StartDate, AssetClassBenchmarkId)
    values (@PortfolioId, s.F_STARTDATE, s.F_BENCHMARK) 
 when not matched by source and @clearExisting != 0 then
    delete
 output  inserted.AssetClassBenchmarkId [InsertedAssetClassBenchmarkId]
    , inserted.StartDate [InsertedStartDate]
    , deleted.AssetClassBenchmarkId [DeletedAssetClassBenchmarkId]
    , deleted.StartDate [DeletedStartDate] 
 into @tmp
4
  • 1
    Welcome to SO. What have you tried so far ? Please check Documentation Previous thread Commented Mar 18, 2020 at 7:23
  • I am using Postgresql 12, Merge is no longer supported in this version. The only approach i know as an alternative is Insert on conflict Commented Mar 18, 2020 at 9:01
  • @swapnilsolanki . . . Sample data, desired results, and an explanation of the logic would help. Commented Mar 18, 2020 at 12:03
  • @GordonLinoff i just want to know the syntax that i may use to replicate same in postgresql Commented Mar 19, 2020 at 4:29

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.