Update table with SET IF / ELSE command
source link: https://www.codesd.com/item/update-table-with-set-if-else-command.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Update table with SET IF / ELSE command
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
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.)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK