CODEWITHSUNDEEP
sql-servert-sql
Thomas Moore
Thomas Moore

Reputation: 981

SQL Conditional Multiplication

I have a table in T-SQL (SQL Server 2016), that looks like the following:

   Flag       Col 1       Col 2    Col 3        
-----------  ---------   -------  -------
   0           33.1        3.9      5.5
   1           20.5        8.1      4.3
   0           15.3        8.33     1.1
   1           12.1        8.4      9.2

This is just a sample of course. But, what I would like to do is update these columns such that if flag = 0, then col 1 stays the same, but col 2 and col 3 = 0. If flag = 1, then col 1 = 0, and col 2 and col 3 stay the same, and so on.

How could one accomplish this in SQL Server?

Thanks.

Upvotes: 2

Views: 135

Answers (2)

HABO
HABO

Reputation: 15852

Assuming that Flag only has values 0 or 1:

update ThyTable
  set Col1 = Col1 * ( 1 - Flag ), Col2 = Col2 * Flag, Col3 = Col3 * Flag

Upvotes: 2

J. D.
J. D.

Reputation: 1591

UPDATE my_table SET 
[Col 1] = CASE WHEN [Flag] = 0 THEN [Col 1] ELSE 0 END, 
[Col 2] = CASE WHEN [Flag] = 1 THEN [Col 2] ELSE 0 END , 
[Col 3] = CASE WHEN [Flag] = 1 THEN [Col 3] ELSE 0 END

Upvotes: 4

Related Questions