如何从其他table得到的资料，填写回去现在的table？

hcong85

1. Update TempCountry
   
2. Set AppleGuid = (Select Guid From TempFruits Where ID = (Select Apple From TempFromExcel Where Country = TempCountry.Country)),
   
3.         OrangeGuid = (Select Guid From TempFruits Where ID = (Select Orange From TempFromExcel Where Country = TempCountry.Country)),
   
4.         PapayaGuid = (Select Guid From TempFruits Where ID = (SelXXXXXya From TempFromExcel Where Country = TempCountry.Country)),
   
5. Where Country In (Select Country From TempFromExcel)

复制代码

没有式过不只能不能用

可能还有更好的办法可是我只能想到这样而已。

musxell

1. CREATE TABLE #TempFruits
   
2. (
   
3.        
   
4.         [Guid] [uniqueidentifier] NULL,
   
5.         [ID] [varchar](512) NULL,
   
6.         [Desc] [varchar](512) NULL
   
7. )
   
8. 
   
9. INSERT INTO #TempFruits([Guid], [ID], [Desc]) VALUES('d001368b-46af-475a-83c3-c8ef40a7de2b', 'USAA', 'UsApple');
   
10. INSERT INTO #TempFruits([Guid], [ID], [Desc]) VALUES('35d16568-7ece-4855-a720-19cc96778dc3', 'AFCO', 'AfricaOrange');
    
11. INSERT INTO #TempFruits([Guid], [ID], [Desc]) VALUES('0107732a-7438-4f55-a76c-7cadccb47f59', 'THAP', 'ThaiPapaya');
    
12. 
    
13. SELECT * FROM #TempFruits
    
14. 
    
15. DROP TABLE #TempFruits
    
16. 
    
17. ---------------------------------------------------------------------------------------------------
    
18. 
    
19. CREATE TABLE #TempCountry
    
20. (
    
21.         [Country] [varchar](512) NULL,       
    
22.         [AppleGuid] [uniqueidentifier] NULL,
    
23.         [OrangeGuid] [uniqueidentifier] NULL,
    
24.         [PapayaGuid] [uniqueidentifier] NULL
    
25. )
    
26. 
    
27. INSERT INTO #TempCountry([Country], [AppleGuid], [OrangeGuid], [PapayaGuid]) VALUES('USA', null, null, null);
    
28. INSERT INTO #TempCountry([Country], [AppleGuid], [OrangeGuid], [PapayaGuid]) VALUES('AFRICA', null, null, null);
    
29. INSERT INTO #TempCountry([Country], [AppleGuid], [OrangeGuid], [PapayaGuid]) VALUES('THAI', null, null,null);
    
30. 
    
31. SELECT * FROM #TempCountry
    
32. 
    
33. DROP TABLE #TempCountry
    
34. 
    
35. ---------------------------------------------------------------------------------------------------
    
36. 
    
37. CREATE TABLE #TempFromExcel
    
38. (
    
39.         [Country] [varchar](512) NULL,       
    
40.         [Apple] [varchar](512) NULL,
    
41.         [Orange] [varchar](512) NULL,
    
42.         [Papaya] [varchar](512) NULL,
    
43. )
    
44. 
    
45. INSERT INTO #TempFromExcel([Country], [Apple], [Orange], [Papaya]) VALUES('USA', 'USAA', null, null);
    
46. INSERT INTO #TempFromExcel([Country], [Apple], [Orange], [Papaya]) VALUES('AFRICA', null, 'AFCO', null);
    
47. INSERT INTO #TempFromExcel([Country], [Apple], [Orange], [Papaya]) VALUES('THAI', null, null, 'THAP');
    
48. 
    
49. SELECT * FROM #TempFromExcel
    
50. 
    
51. DROP TABLE #TempFromExcel

复制代码

#TempFruits 和 #TempCountry
这两个是我现在有的table

#TempFromExcel
是我Import资料，来自EXCEL档案

问题来了，现在我有来自EXCEL档案的资料
我该如何从#TempFromExcel join 去 #TempFruits，找出Guid之后
再Update #TempCountry 这个 table呢？

如果做for loop？
一行接一行update

musxell

原帖由 hcong85 于 2-12-2008 09:03 PM 发表
Update TempCountry
Set AppleGuid = (Select Guid From TempFruits Where ID = (Select Apple From TempFromExcel Where Country = TempCountry.Country)),
        OrangeGuid = (Select Guid From TempFruits Where ID ...

謝謝你
但是我又有問題了，在下面

CREATE TABLE #TempFruits
(
      
        [Guid] [uniqueidentifier] NULL,
        [ID] [varchar](512) NULL,
        [Desc] [varchar](512) NULL
)

INSERT INTO #TempFruits([Guid], [ID], [Desc]) VALUES('d001368b-46af-475a-83c3-c8ef40a7de2b', 'USAA', 'UsApple');
INSERT INTO #TempFruits([Guid], [ID], [Desc]) VALUES('35d16568-7ece-4855-a720-19cc96778dc3', 'AFCO', 'AfricaOrange');
INSERT INTO #TempFruits([Guid], [ID], [Desc]) VALUES('0107732a-7438-4f55-a76c-7cadccb47f59', 'THAP', 'ThaiPapaya');

SELECT * FROM #TempFruits



---------------------------------------------------------------------------------------------------

CREATE TABLE #TempCountry
(
        [Country] [varchar](512) NULL,      
        [AppleGuid] [uniqueidentifier] NULL,
        [OrangeGuid] [uniqueidentifier] NULL,
        [PapayaGuid] [uniqueidentifier] NULL
)

INSERT INTO #TempCountry([Country], [AppleGuid], [OrangeGuid], [PapayaGuid]) VALUES('USA', null, null, null);
INSERT INTO #TempCountry([Country], [AppleGuid], [OrangeGuid], [PapayaGuid]) VALUES('AFRICA', null, null, null);
INSERT INTO #TempCountry([Country], [AppleGuid], [OrangeGuid], [PapayaGuid]) VALUES('THAI', null, null,null);

SELECT * FROM #TempCountry


---------------------------------------------------------------------------------------------------

CREATE TABLE #TempFromExcel
(
        [Country] [varchar](512) NULL,      
        [Apple] [varchar](512) NULL,
        [Orange] [varchar](512) NULL,
        [Papaya] [varchar](512) NULL,
)

INSERT INTO #TempFromExcel([Country], [Apple], [Orange], [Papaya]) VALUES('USA', 'USAA', null, null);
INSERT INTO #TempFromExcel([Country], [Apple], [Orange], [Papaya]) VALUES('AFRICA', null, 'AFCO', null);
INSERT INTO #TempFromExcel([Country], [Apple], [Orange], [Papaya]) VALUES('THAI', null, 'AFCO', 'THAP');

SELECT * FROM #TempFromExcel

---------------------------------------------------------------------------------------------------

Select DISTINCT * From (
SELECT E.Country,
            CASE    WHEN F.ID = E.Apple THEN F.Guid END AS AppleGuid,
            CASE WHEN F.ID = E.Orange THEN F.Guid END OrangeGuid,
            CASE WHEN F.ID = E.Papaya THEN F.Guid END AS  PapayaGuid
FROM #TempFruits F INNER JOIN #TempFromExcel E
ON (F.ID = E.Orange OR F.ID = E.Apple OR F.ID = E.Papaya)
) A
Group By Country, AppleGuid, OrangeGuid, PapayaGuid

DROP TABLE #TempFruits
DROP TABLE #TempCountry
DROP TABLE #TempFromExcel

谁能帮我看看呢？
execute上面的SQL
出现了两个THAI，我要GROUP它们成一笔
如何呢？
出来的答案是
THAI只有一笔，guid自动和拼