Home SQL compare to identical tables copy value from certain columns
Reply: 2

SQL compare to identical tables copy value from certain columns

Dave Vleugel
1#
Dave Vleugel Published in 2018-01-11 13:29:04Z

I have two identical tables (x) and (y) (I made a backup from table x to table y) I deleted everything in table x to regenerate new rows (I added restmilk_3 as extra parameter).

Now my ID is new in table x (It moved because i added restmilk_3), so i want to copy the value from table y to x by name and RID (Recipe ID)

So how it looks like:

**table y**

ID - RID            - Name        - Value
1  - Cheese_rec_100 - restmilk_1  - cheese1
2  - Cheese_rec_100 - restmilk_2  - cheese3
3  - Cheese_rec_100 - milkamount  - 5000
..
100- Cheese_rec_100 - milkdensity - 0.46


**table x** (We added restmilk 3 as a new parameter):

ID - RID            - Name        - Value
1  - Cheese_rec_100 - restmilk_1  - NULL
2  - Cheese_rec_100 - restmilk_2  - NULL
3  - Cheese_rec_100 - restmilk 3  - NULL
4  - Cheese_rec_100 - milkamount  - NULL
..
100- Cheese_rec_100 - milkdensity - NULL


After the query:

ID - RID            - Name        - Value
1  - Cheese_rec_100 - restmilk_1  - cheese1
2  - Cheese_rec_100 - restmilk_2  - cheese3
3  - Cheese_rec_100 - restmilk 3  - NULL
4  - Cheese_rec_100 - milkamount  - 5000
..
100- Cheese_rec_100 - milkdensity - 0.46

So I can't use ID as key to copy the Value but I need to check if the RID and Name is equal from both tables.

I tryed alot of things but I dont have the skills/experience to solve this puzzle.

I use Microsoft SQL Server Management Studio Database collation: Latin1_General_CI_AS Compatability level: SQL Server 2012

I have all rights as administrator.

Example existing table:

ID  RID Version NodeID  Name    TagAddress  Value
854389  Cheese_Rec_00100    61  8   _RestMilk14 -   0
854390  Cheese_Rec_00100    61  8   _RestMilk15 -   0
854391  Cheese_Rec_00100    61  8   _RestMilk16 -   0
854392  Cheese_Rec_00100    61  8   _RestMilk17 -   0
854393  Cheese_Rec_00100    61  8   _RestMilk18 -   0
854394  Cheese_Rec_00100    61  8   _Starter1388Delay   -   240
854395  Cheese_Rec_00100    61  8   _Starter1388Target  -   90
854396  Cheese_Rec_00100    61  8   _Starter1389Delay   -   0
854397  Cheese_Rec_00100    61  8   _Starter1389Target  -   0
854398  Cheese_Rec_00100    61  8   _TransPercentageFat -   0,94
854399  Cheese_Rec_00100    61  8   _TransPercentProtein    -   0,935
854400  Cheese_Rec_00100    61  8   _VATPostWaterTempSP -   33,1
854401  Cheese_Rec_00100    61  8   _VATRecipeNo    -   19
854402  Cheese_Rec_00100    61  8   _WheyCreamLimit -   2
854403  Cheese_Rec_00100    61  8   _AmountOfMilk   -   1000
854404  Cheese_Rec_00100    61  8   _AverageWeightAt15D -   13,3
854405  Cheese_Rec_00100    61  8   _CaCl2Delay -   600
854406  Cheese_Rec_00100    61  8   _CaCl2Target    -   0,1337
VipinK
2#
VipinK Reply to 2018-01-11 15:45:04Z

You can check/ensure if RID and NAME columns are equal in table X and Y by using UNION ..

SELECT RID,Name FROM y UNION SELECT RID,Name FROM X

If number of rows returned are equal to number of rows in table X.. Then Update as you mentioned in your comments.

UPDATE X SET X.VALUE=Y.VALUE FROM X INNER JOIN Y ON Y.RID=X.RID AND Y.NAME=X.NAME

Hope this helps.

Dave Vleugel
3#
Dave Vleugel Reply to 2018-01-12 08:46:58Z

As final this is my code:

update [TPMDB].[dbo].[RecipeNodeParameter]

set 
    [TPMDB].[dbo].[RecipeNodeParameter].Value = [TPMDB].[dbo].[RecipeNodeParameter_2].Value,

    [TPMDB].[dbo].[RecipeNodeParameter].MaterialTagAddress = [TPMDB].[dbo].[RecipeNodeParameter_2].MaterialTagAddress,

    [TPMDB].[dbo].[RecipeNodeParameter].[Material_PLCID] = [TPMDB].[dbo].[RecipeNodeParameter_2].[Material_PLCID] ,

    [TPMDB].[dbo].[RecipeNodeParameter].[Material_SiteMaterialAlias] = [TPMDB].[dbo].[RecipeNodeParameter_2].[Material_SiteMaterialAlias],

    [TPMDB].[dbo].[RecipeNodeParameter].[Material_Name] = [TPMDB].[dbo].[RecipeNodeParameter_2].[Material_Name]  


From [TPMDB].[dbo].[RecipeNodeParameter] 


    INNER Join [TPMDB].[dbo].[RecipeNodeParameter_2] ON
    [TPMDB].[dbo].[RecipeNodeParameter_2].RID = [TPMDB].[dbo].[RecipeNodeParameter].RID

AND

    [TPMDB].[dbo].[RecipeNodeParameter_2].Name = [TPMDB].[dbo].[RecipeNodeParameter].Name

WHERE[TPMDB].[dbo].[RecipeNodeParameter].RID like '%cheese_rec_00100%'
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.328461 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO