insert data from each row in column into table if another column has the same value

Question

I need to transfer 17,000 'ISSI' values from table 1 -> table 2 using a comparison of IDs'. Not sure if a loop is required as the IDs are out of order.

Table 1: - sysdba.C_AccountTable

AccountProductID |    ISSI  | 
-----------------------------
 1QWEqwe         |    113   |
 2qweqwrdsd      |    436   |
 1Eqwe           |    113   |

Table 2: - dbo.AssetTable

CustomerProductID | ISSI |
--------------------------
    1QWEqwe       |      |
    1Eqwe         |      |
2qweqwrdsd        |      |

Expected Result- dbo.AssetTable

CustomerProductID | ISSI |
--------------------------
    1QWEqwe       | 113  |
    1Eqwe         | 113  |
2qweqwrdsd        | 436  |

(17,000 Rows to be changed)

Possible duplicate of [Copy data into another table](https://stackoverflow.com/questions/13237623/copy-data-into-another-table) — Hina Khuman, Dec 21 '17 at 10:01
I have copied the ID from table 1 to table 2. Now i need copy the ISSI values from table 1 > 2 if the ID is the same. — Mrparkin, Dec 21 '17 at 10:18

score 0 · Accepted Answer · answered Dec 21 '17 at 10:10

This might be the expected Result

DECLARE @C_AccountTable TABLE (AccountProductID VARCHAR(100),ISSI    INT)
INSERT INTO @C_AccountTable
 SELECT '1QWEqwe'         ,    113   UNION ALL
 SELECT '2qweqwrdsd'      ,    436   UNION ALL
 SELECT '1Eqwe'           ,    113   

DECLARE @AssetTable TABLE(CustomerProductID VARCHAR(100) , ISSI INT)
INSERT INTO @AssetTable
 SELECT   '1QWEqwe',NULL       UNION All     
 SELECT   '1Eqwe'  ,NULL      

 SELECT * FROM @AssetTable
 UPDATE A
 SET A.ISSI=C.ISSI
 FROM @AssetTable A
    INNER JOIN @C_AccountTable C
        ON c.AccountProductID=A.CustomerProductID


 SELECT * FROM @AssetTable

Result Before Update

CustomerProductID   ISSI
 -------------------------
    1QWEqwe         NULL
    1Eqwe           NULL

Result After Update

CustomerProductID   ISSI
-------------------------
1QWEqwe             113
1Eqwe               113

score 0 · Answer 2 · answered Dec 21 '17 at 10:21

UPDATE  T2
SET T2.ISSI = T1.ISSI  
FROM dbo.AssetTable as T2 INNER JOIN sysdba.C_AccountTable as T1
ON T1.AccountProductID  = T2.CustomerProductID;

I have checked this experiment

-- Table Creating start

declare @t table(Code varchar(20), name varchar(50))
declare @t1 table(Code varchar(20), name varchar(50))

DECLARE @counter INT = 1;
INSERT INTO @t ([Code],[Name]) VALUES( @counter , 'val 1');
set @counter = @counter + 1
INSERT INTO @t ([Code],[Name]) VALUES( @counter , 'val 2');
set @counter = @counter + 1
INSERT INTO @t ([Code],[Name]) VALUES( @counter , 'val 3');
set @counter =  1
INSERT INTO @t1 ([Code],[Name]) VALUES( @counter , '');
set @counter = @counter + 1
INSERT INTO @t1 ([Code],[Name]) VALUES( @counter , '');
set @counter = @counter + 1
INSERT INTO @t1 ([Code],[Name]) VALUES( @counter , '');


select * from @t
select * from @t1

-- Table Creating End

UPDATE  T2
SET    T2. Name = T1 .Name
FROM   @t1 as T2 INNER JOIN @t as T1
ON     T1. Code = T2 .Code;

select * from @t1

insert data from each row in column into table if another column has the same value

2 Answers2