Update one column of Table A and copy the data from another table which is Table B-CodePudding

I am trying to insert data to another table but is has some error.

Source Table:

Column_name     Type    Computed    Length
Are_CompanyID   char    no           2             
Are_AssetCode   char    no          20      
Are_DeptCode    varchar no         100

Table to be inserted:

Column_name             Type    Computed    Length
Foi_CompanyID           char    no           2
Foi_AssetCode           char    no           20
Foi_DepartmentInCharge  varchar no           100

CompanyID and AssetCode are Primary key.

My SQL query:

INSERT INTO E_AssetRegistry(Are_DeptCode)
SELECT Foi_DepartmentInCharge
FROM E_FixedAssetOtherInfo
INNER JOIN E_AssetRegistry ON Foi_AssetCode=Are_AssetCode AND Foi_CompanyID=Are_CompanyID

Error:Cannot insert the value NULL into column 'Are_CompanyID', table 'NFC_THI.dbo.E_AssetRegistry'; column does not allow nulls. INSERT fails. The statement has been terminated.

CodePudding user response：

Okay I realize if you only want to Edit/insert data into one column you should use Update instead of INSERT TO .

SQL:
UPDATE E_AssetRegistry
SET Are_DeptCode = 
(SELECT Foi_DepartmentInCharge
FROM E_FixedAssetOtherInfo where Foi_AssetCode=Are_AssetCode AND Foi_CompanyID=Are_CompanyID)

CodePudding user response：

An alternative is to use the UPDATE ... SET ... FROM ... syntax.

UPDATE A
SET Are_DeptCode = F.Foi_DepartmentInCharge
FROM E_AssetRegistry A
JOIN E_FixedAssetOtherInfo F
    ON F.Foi_AssetCode = A.Are_AssetCode
    AND F.Foi_CompanyID = A.Are_CompanyID

This has a similar effect as Its_Me's answer, but can be expanded to handle more complicated updates. Differences to be aware of:

If there are no matches, this syntax will not update rows with no matches. The subselect syntax will assign nulls.
If there are multiple matches, this syntax will assign one arbitrarily. The subselect form will throw an error during execution.