CODEWITHSUNDEEP
sql-serverif-statementcase
Christian
Christian

Reputation: 7

Update table with SET command IF / ELSE

I'm trying to update a table and i want to runt 2 different SET senarios depending on a stock value.

Working CODE that does one senario.

UPDATE dbo.ar

    SET webPublish = '0', 
        ArtProdKlass = '9999',
        VaruGruppKod = '9999',
        ItemStatusCode = '9' --Utgått ur sortimentet+
        FROM tmp_9999
        WHERE ar.ArtNr = tmp_9999.art AND ar.lagsadloartikel < '1'

What i would like to do is that IF last statement (ar.lagsaldoartikel) is >'1'
Then i would like it to run this SET:

  SET   webPublish = '1', 
        ArtProdKlass = '1',
        VaruGruppKod = '9999',
        ItemStatusCode = '8'

So something like this i have tested:

    IF  AR.lagsaldoartikel < '1'

    SET webPublish = '0', 
        ArtProdKlass = '9999',
        VaruGruppKod = '9999',
        ItemStatusCode = '9' --Utgått ur sortimentet+

    FROM tmp_9999
    WHERE ar.ArtNr = tmp_9999.art --Väljer ut artiklar som enbart finns i textfilen och har lagersaldo mindre än 1
ELSE

    SET webPublish = '1', 
        ArtProdKlass = '1',
        VaruGruppKod = '9999',
        ItemStatusCode = '8' --Utgått ur sortimentet

    FROM tmp_9999
    WHERE ar.ArtNr = tmp_9999.art --Väljer ut artiklar som enbart finns i textfilen och har lagersaldo mindre än 1

Upvotes: 0

Views: 34

Answers (2)

Luaan
Luaan

Reputation: 63772

If is part of the procedural T-SQL. You can't use procedural statements inside the relational ones - the only way to use if would be to have two separate update statements, each in one branch of the if. However, that's a bad idea - it's not concurrency-safe.

One way to accomplish what you're trying to do is to use the case statement instead - that's just an expression, so it can be used in the set clause just fine:

set webPublish = case when AR.lagsaldoartikel < '1' then '0' else '1' end

(etc. for the other arguments).

However, I'd like to warn you - this is almost certainly a bad idea. It's probably going to back-fire on you soon in the future, when you realize that there's ten different conditions and a hundred different possible values you might want. Consider using a more idiomatically relational way of doing this - for example, taking the conditions and arguments from a different table - it's not necessary now, but if you ever find your conditions are expanding out of reasonable size, remember to consider changing the whole structure of the command if needed.

Upvotes: 1

Alex K.
Alex K.

Reputation: 175956

Using CASE:

UPDATE dbo.ar
    SET webPublish = '0', 
        ArtProdKlass = '9999',
        VaruGruppKod = '9999',
        ItemStatusCode = CASE WHEN AR.lagsaldoartikel < '1' THEN '9' ELSE '8' END
        FROM tmp_9999
        WHERE ar.ArtNr = tmp_9999.art

(If ItemStatusCode et al are numeric you should treat them as such.)

Upvotes: 1

Related Questions