CODEWITHSUNDEEP
sqlt-sql
shaadi
shaadi

Reputation: 171

How to update table column's value from another table in sequence when table do not relate with each other

I have two tables (A and B). I want to update col3_A with the value col3_B from table B. Col3_B value is in sequence.So Col3_A has to be updated in sequence as of col3_B.

Table A:

+--------+--------+--------+
| col1_A | col2_A | col3_A |
+--------+--------+--------+
| A      |      1 | 5      |
| B      |      1 | 3      |
| C      |      1 | 2      |
| D      |      1 | 1      |
+--------+--------+--------+

Table B:

+--------+
| col3_B |
+--------+
|      6 |
|      7 |
|      8 |
|      9 |
+--------+

Required Result:

+--------+--------+--------+
| col1_A | col2_A | col3_A |
+--------+--------+--------+
| A      |      1 |      6 |
| B      |      1 |      7 |
| C      |      1 |      8 |
| D      |      1 |      9 |
+--------+--------+--------+

Problem my code only set first two value from col3_B to all col3_A columns and only two value repeat e.g 

+--------+
| Col3_A |
+--------+
|      6 |
|      7 |
|      6 |
|      7 |
+--------+

my Code:

update A
set A.col2_A = '1', A.col3_A = B.col3_B 
from (select ROW_NUMBER() OVER (ORDER BY [col] ASC) AS col3_B 
from tableb) B
where A.col1_A in 
(
'A',
'B',
'C',
'D'
)

Upvotes: 0

Views: 883

Answers (2)

Aparna
Aparna

Reputation: 286

You could try the below option as well.Make sure the way we order the records are right though since I am not sure of the business logic in doing that 

with temp as 
(
   select col1_a,col2_a,col3_a ,
    row_number() over( order by col2_a ) as rn 
   from tablea 
),
 temp1 as 
 ( 
    select col3_b ,row_number() over( order by col3_b ) as rn 
    from tableb
 )
select temp.col1_a,temp.col2_a,temp.col3_a,temp.rn,temp1.col3_b 
into #tt 
from temp join temp1 
on temp.rn=temp1.rn;

 update tablea
 set col3_a=( select tt.col3_b from #tt tt where tt.col3_a=tablea.col3_a)

Upvotes: 1

Aparna
Aparna

Reputation: 286

Add a unique no to both table1 and table2 and then update the table1 based on table2.

Now the update will be 

 update table1
 set col3_a=( select col3_b from tableb where a.unqiueid=b.uniqueid);

Since col3_a is a character and col3_b is an integer,we need to make sure it does not impact anything else

To create a Unique ID column follow the below steps

step 1 : create a copy of table a using the below query :

create table table1_cp
 (
  unique_id integer identity(1,1) primary key,
  col1_A varchar(25),
  col2_a varchar(100),
  col3_a varchar(100)
 );

Step 2 :Insert the records from table1 into the cp table 

select * into table1_cp from table1;

Step 3 :Now you can see that the table1_cp has the unique id automatically populated .Now rename the tabl1_cp as table1 after dropping the table1 

drop table table1;
sp_rename('table_cp','table1');

Step 4 :Repeat the same for tableb as well by creating a copy table adding the idnetity column and then inserting records form the original table and finally rename the table

This should be used just for updating purpose or to uniquely identify a record Hope this helps

Upvotes: 0

Related Questions