How to inset one table colume data into another table column

Question

Can u help me how to insert Column A data into col3 at place of null.

See the attachment.

TABLE TAB1
--------------
col1 col2 col3
5    7    NULL
8    11   NULL
3    6    NULL
2    12   NULL

TABLE TAB2
-----------
ColA    CoB
7       5
18      8
24      3
36      2

Desire Output Like

col1 col2 col3
5    7    7
8    11   18
3    6    24
2    12   36

This is called commutative sum.

Is there a matching column to use? Are the record counts the same? If there is no matching column, how do you know the order of the records in both tables? If the record count does not match and no ordering column, how do you know which value goes where? — Pred, Feb 03 '17 at 11:26
Possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Pred, Feb 03 '17 at 11:34

sam chaudhari · Answer 1 · 2017-02-03T11:52:32.347

1

1. It's work for same table.

update TABLENAME set col3=col2

**2.**For inserting one table column data into another table

INSERT into tab1(col1) select col1 from tab2

edited Feb 03 '17 at 11:52

answered Feb 03 '17 at 11:42

sam chaudhari

756
1
12
24

score 0 · Answer 2 · edited Feb 03 '17 at 15:11

0

Try this:

Update tab1
set Col3=tab2.ColA 
from tab1 
inner join tab2 on tab1.col1=tab2.colB

edited Feb 03 '17 at 15:11

arghtype

4,376
11
45
60

answered Feb 03 '17 at 11:37

cloudsafe

2,444
1
8
24

score 0 · Answer 3 · answered Feb 03 '17 at 11:48

From what I can tell, you want a cumulative sum from the second column:

with toupdate as (
      select t1.*,
             sum(t1.col2) over (order by ??) as cume_col2
      from tab1 t1
     )
update toupdate
    set col3 = cume_col2;

In order to do a cumulative sum, you need a column that specifies the order for the sum. Your data as shown does not have an appropriate column.

EDIT:

Oh, I see. The ordering comes from the second table:

with toupdate as (
      select t1.*,
             sum(t1.col2) over (order by t2.cola) as cume_col2
      from tab1 t1 join
           tab2 t2
           on t1.col1 = t2.colb
     )
update tab1
    set col3 = toupdate.cume_col2
    from tab1 join
         toupdate
         on tab1.col1 = toupdate.col1;

How to inset one table colume data into another table column

3 Answers3