Search code examples
sqlsql-serversql-updatecase-wheniif

Update statement with IIF not upading

I have a simple table with order-number, article-number and a rank:

select * from tbl_temp_ranked

enter image description here

I want to update another table only with rank=2. So I use the following update-statement

update hbs
set hbs.[2ND_NEXT_ORDER_Nr]=iif(ranked=2, ranked, null)
from TBL_HAKA_BASE_STAGE hbs
join tbl_temp_ranked tmp
on hbs.artikelnummer=tmp.artikelnummer
where hbs.Artikelnummer=115996

My target-table (TBL_HAKA_BASE_STAGE) is never updating. It only updates when I write

iif(ranked=1, ranked, null)

Only in that case my target table is updating! Writing ranked<>1, ranked>1, ranked!=1, ranked=2 doesn't update my table. Same for case when-conditions in this SET-Statement. Casting didn't help also!

I could add this condition in my WHERE-statements, this helps, yes, but this short SQL-example is just a very short code in a large statement, so this is not a great solution for me. This would make me repeat all of the code over and over again.

Here are the structures for both tables, maybe it help

CREATE TABLE [dbo].[tbl_temp_ranked](
    [auftragsnummer] [float] NULL,
    [artikelnummer] [float] NULL,
    [ranked] [bigint] NULL
)

CREATE TABLE [dbo].[TBL_HAKA_BASE_STAGE](

    [Artikelnummer] [float] NULL,
    [NEXT ORDER Nr] [float] NULL
)

It is SQL server 2017.

Solution

  • You have duplicates in the "ranked" table. I think you want a left join:

    update hbs
    set hbs.[2ND_NEXT_ORDER_Nr] = tmp.ranked
    from TBL_HAKA_BASE_STAGE hbs left join
         tbl_temp_ranked tmp
         on hbs.artikelnummer = tmp.artikelnummer and tmp.ranked = 2
    where hbs.Artikelnummer = 115996;