CODEWITHSUNDEEP
sql-servert-sql
Daemon
Daemon

Reputation: 27

Use if clause in sub query to set value for update

I have this table of data

id | name |  Car  |  Bike  |
------------------------------------
1  | me   |   1   |    0   |
2  | you  |   0   |    1   |

And I want to update it in my tax table from:

tax_id | name | net  |  Car | Bike | Home | Total |
---------------------------------------------------
   1   |  me  | 100  | NULL | NULL |  10  |   10  |
   2   |  you | 1000 | NULL | NULL |  100 |   100 |

to:

tax_id | name | net  | Car | Bike | Home | Total |
---------------------------------------------------
   1   |  me  | 100  |  5  |   0  |  10  |   15  |
   2   |  you | 1000 |  0  |   20 |  100 |   120 |

with rules

I tried to use: 

UPDATE tax
SET
    tax.Car = (
               IF (SELECT Car FROM DATA WHERE ID = 1) > 1
                   BEGIN
                       RETURN tax.net * 0.5
                   END 
               ),
     tax.Bike = (
               IF (SELECT Bike FROM DATA WHERE ID = 1) > 1
                   BEGIN
                       RETURN tax.net * 0.2
                   END 
               ),
WHERE ID = 1

Upvotes: 0

Views: 58

Answers (1)

Dale K
Dale K

Reputation: 27202

What follows is a possible solution. Points to note:

  1. Please setup any future SQL questions in this way (i.e. creating the sample data ready to use) as makes it much easier to answer and you will get more/faster assistance.
  2. The IF statement (in SQL Server) is for control flow only, not for conditional values in statements. Instead use CASE
  3. I have assumed you meant 0.05 and 0.02 respectively (as opposed to 0.5 & 0.2) as this was the only way to get the results you require.
  4. Note the sub-query, used to allow us to make use of intermediate results to calculate the Total column. Which is then joined onto the original table to allow us to apply the updates.
    declare @Test table (id int, [Name] varchar(32), Car bit, Bike bit);
    declare @Tax table (tax_id int, [Name] varchar(32), Net money, Car money, Bike money, Home money, Total money);

    insert into @Test (id, [Name], Car, Bike)
    values (1, 'me', 1, 0), (2, 'you', 0, 1);

    insert into @Tax (tax_id, [Name], Net, Car, Bike, Home, Total)
    values (1, 'me', 100, null,null, 10, 10), (2, 'you', 1000, null, null, 100, 100);

    --select * from @Test;
    --select * from @Tax;

    update T set
      Car = Y.Car
      , Bike = Y.Bike
      , Home = Y.Home
      , Total = Y.Total
    from @Tax T
    inner join (
    select tax_id, Car, Bike, Home,  Car + Bike + Home Total
      from (
        select tax_id
          , case when exists (select 1 from @Test T where T.id = tax_id and T.Car = 1) then Net else 0 end * 0.05 Car
          , case when exists (select 1 from @Test T where T.id = tax_id and T.Bike = 1) then Net else 0 end * 0.02 Bike
          , Net * 0.1 Home
        from @Tax
      ) X
    ) Y on Y.tax_id = T.tax_id;

    select * from @Tax;

Returns:

tax_id  Name    Net Car Bike    Home    Total
1   me  100.00  5.00    0.00    10.00   15.00
2   you 1000.00 0.00    20.00   100.00  120.00

Upvotes: 1

Related Questions