SQL update from other table with a condition

user_13051158 0
Viewed 48

I´m trying to sum all values from various id with a condition in a SQL server database.

i can do it with a select to an fno and one type of tabiva, but when try to do the update i can´t.

I try and search, but is always giving me error.

Table 1

|fno|TABIVA|VATTOTAL|
|100|   1  |  1.35  |
|100|   1  |  2.00  |
|100|   3  |  1.50  |
|200|   2  |  4.00  |
|300|   1  |  3.00  |
|400|   3  |  1.50  |
|400|   2  |  2.00  |
|400|   1  |  1.20  |

Table 2

|fno|TABIVA1|TABIVA2|TABIVA3|
|100| 0.00  | 0.00  |  0.00 |
|200| 0.00  | 0.00  |  0.00 | 
|300| 0.00  | 0.00  |  0.00 |
|400| 0.00  | 0.00  |  0.00 |

Result expected after the update on table 2

|fno|TABIVA1|TABIVA2|TABIVA3|
|100| 3.35  | 0.00  |  1.50 |
|200| 0.00  | 1.50  |  0.00 |
|300| 3.00  | 0.00  |  0.00 |
|400| 1.20  | 2.00  |  1.50 |
sql-server sql
3 Answers

You can use a MERGE statement to get this done.

merge
  into table2 a
using (select fno
              ,sum(case when TABIVA=1 then VATTOTAL end) as tabiva1
              ,sum(case when TABIVA=2 then VATTOTAL end) as tabiva2
              ,sum(case when TABIVA=3 then VATTOTAL end) as tabiva3
        from table1
        group by fno
       )x
   on a.fno=b.fno 
when matched then
update
   set a.tabiva1=x.tabiva1
      ,a.tabiva2=x.tabiva2
      ,a.tabiva3=x.tabiva3
user_71370900

You can use cte with join :

with UpdtCte as (
     select fno, 
            sum(case when TABIVA = 1 then VATTOTAL else 0 end) as tabiva1,
            sum(case when TABIVA = 2 then VATTOTAL else 0 end) as tabiva2,
            sum(case when TABIVA = 3 then VATTOTAL else 0 end) as tabiva3
     from t1
     group by fno
)
update t2
      set t2.tabiva1 = updt.tabiva1,
          t2.tabiva1 = updt.tabiva2,
          t2.tabiva1 = updt.tabiva3
from t2 inner join
     UpdtCte updt
     on updt.fno = t2.fno;

However, it fine with run-time (use select & do aggregation whenever required), update is really not needed, by this way t2 will have invalid data if table1 changed :

select fno, 
       sum(case when TABIVA = 1 then VATTOTAL else 0 end) as tabiva1,
       sum(case when TABIVA = 2 then VATTOTAL else 0 end) as tabiva2,
       sum(case when TABIVA = 3 then VATTOTAL else 0 end) as tabiva3
from t1
group by fno;
user_55294450

Try something like this:

update t2 
set t2.tabiva1 = case when t1.tabiva = 1 then t1.vattotal else 0 end
tabiva2 = case when t1.tabiva = 2 then t1.vattotal else 0 end
tabiva3 = case when t1.tabiva = 3 then t1.vattotal else 0 end

from table2 t2
inner join Table1  t1 
on t2.fno = t1.fno
user_40468510
Related