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

SQL compare to identical tables copy value from certain columns

user2789
1#
user2789 Published in July 18, 2018, 10:30 pm

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
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO