February 14, 2009 at 3:55 pm
I HAVE TWO TABLES AS SHOWN BELOW:
Table One
rCde pCde POD aitMonth hCR Activity Cost SpecialtyCode HRG35_Code
-------------------- -------------------- -------------------- ------------- ---------------- A A Pea 2 1 NULL NULL NULL NULL
Table two
rCde pCde POD aitMonth hCR Activity Cost SpecialtyCode HRG35_Code
-------------------- -------------------- -------------------- ------------- ---------------- A A Pea 2 1 3 200.0000 A B
A A Pea 2 1 1 50.0000 C D
A A Pea 2 1 6 100.0000 E F
How can I update table one such that I end up with three records in table one that conatin the three sets of activity, cost,SpecialtyCode and HRG35_Code fields from table two. I have cuurently tried an update statement based on a join between table one and two where rCde, pCde,POD,aitMonth,hCR match. HOwever I only get I row updated a there is only 1 distinct row in table one. SOmeone has suggested I use a cursor but I am not sure how it will work here. Any help would be much appreciated.
February 14, 2009 at 5:02 pm
Do you want table 1 to end up exactly matching the contents of table 2?
If so you want to update the existing row then do an insert selecting matching rCde, pCde,POD,aitMonth,hCR match and non matching activity.
I think.....
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
February 15, 2009 at 1:37 am
I dont want table1 to match the contents of table3 exactly. I just want three records in table 1 where the 4 fields match the fields in table2. In my real table there are other fields in table1 which i havent included. There are also 64000 records in my real table. I used the example with few records just to illustrate my problem.
February 15, 2009 at 5:49 am
Your question is very vage and unclear. It looks like you need an insert statement and not an update statement. If I’m wrong pleas take few minutes to read the article in my signature. After that ask your question again, but this time instead of drawing 2 tables, write a small script that creates both tables, then insert test data and let us know the result that you expect after we modify the data.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 15, 2009 at 10:59 am
Here is the script that I am using:
CREATE TABLE #zzPBC_Budget_v_Actuals
(
providerCode varchar(20)
,practiceCode varchar(20)
,POD varchar(20)
,activityMonth tinyint
,highCostRiskPool bit
,actualActivity int
,actualCost money
,SpecialtyCode varchar(20)
,HRG35_Code varchar(20)
)
INSERT INTO #zzPBC_Budget_v_Actuals
SELECT 'A', 'A', 'Pea', 2, 1, NULL, NULL, NULL, NULL UNION ALL
SELECT 'A', 'A', 'Low', 1, 1, NULL, NULL, NULL, NULL
CREATE TABLE #vw_zzPBC_Report_Actuals
(
providerCode varchar(20)
,practiceCode varchar(20)
,POD varchar(20)
,activityMonth tinyint
,highCostRiskPool bit
,Activity int
,Cost money
,SpecialtyCode varchar(20)
,HRG35_Code varchar(20)
)
INSERT INTO #vw_zzPBC_Report_Actuals
SELECT 'A', 'A', 'Pea', 2, 1, 3, 200, 'A', 'B' UNION ALL
SELECT 'A', 'A', 'Pea', 2, 1, 1, 50, 'C', 'D' UNION ALL
SELECT 'A', 'A', 'Pea', 2, 1, 6, 100, 'E', 'F' UNION ALL
SELECT 'A', 'B', 'Low', 1, 1, 6, 100, 'E', 'F' UNION ALL
SELECT 'A', 'B', 'Low', 1, 1, 6, 300, 'A', 'M'
UPDATE #zzPBC_Budget_v_Actuals
SET
actualActivity = A.activity,
actualCost = A.cost,
SpecialtyCode = A.SpecialtyCode,
HRG35_Code = A.HRG35_Code
FROM
#zzPBC_Budget_v_Actuals BvA INNER JOIN #vw_zzPBC_Report_Actuals A ON
BvA.providerCode = A.providerCode AND
BvA.practiceCode = A.practiceCode AND
BvA.POD = A.POD AND
BvA.activityMonth = A.activityMonth AND
BvA.highCostRiskPool = A.highCostRiskPool
SELECT * FROM #zzPBC_Budget_v_Actuals
I actually want 5 rows returned when I select * from #zzPBC_Budget_v_Actuals after my update statement. I understand that I cannot do this at the moment based on my join because there are only two distinct records where an update can occur. Therefore how can I acheive that result.
February 15, 2009 at 12:48 pm
[font="Verdana"]Here's the resultant code I get. You need to add this after your update.
insert into #zzPBC_Budget_v_Actuals(
providerCode,
practiceCode,
POD,
activityMonth,
highCostRiskPool,
actualActivity,
actualCost,
SpecialtyCode,
HRG35_Code
)
select A.providerCode,
A.practiceCode,
A.POD,
A.activityMonth,
A.highCostRiskPool,
A.Activity,
A.Cost,
A.SpecialtyCode,
A.HRG35_Code
from #vw_zzPBC_Report_Actuals A
left outer join
#zzPBC_Budget_v_Actuals BvA
on A.providerCode = BvA.providerCode and
A.practiceCode = BvA.practiceCode and
A.POD = BvA.POD and
A.activityMonth = BvA.activityMonth and
A.highCostRiskPool = BvA.highCostRiskPool
where BvA.providerCode is null and
BvA.practiceCode is null and
BvA.POD is null and
BvA.activityMonth is null and
BvA.highCostRiskPool is null;
Note that it only returns 4 rows. This is because two of the rows in the #zzPBC_Budget_v_Actuals are not unique across the join values.
[/font]
February 15, 2009 at 1:40 pm
You need to use an insert statement and not an update statement. You didn’t specify how to decide which records will be inserted into zz table. I think that you wanted to insert only the records that had the same values in the first 4 columns. If I was correct, then you should use the insert statement bellow which will insert 3 records into your table. Since you already have 2 records in the table, the select will return 5 records. If I misunderstood you, pleas clarify the way that we should decide which records should be inserted into the table
INSERT INTO #zzPBC_Budget_v_Actuals (providerCode, practiceCode, POD, activityMonth,
highCostRiskPool, actualActivity, actualCost, SpecialtyCode, HRG35_Code)
SELECT v.providerCode, v.practiceCode, v.POD, v.activityMonth,
v.highCostRiskPool, v.Activity, v.Cost, v.SpecialtyCode, v.HRG35_Code
FROM #zzPBC_Budget_v_Actuals as Z inner join #vw_zzPBC_Report_Actuals as V
on Z.providerCode = V.ProviderCode and Z.PracticeCode = V.practiceCode
and Z.POD = V.POD and Z.activityMonth = V.activityMonth
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2009 at 3:43 am
Hi this is nearly what I want. After adding the insert statement the final output in #zzPBC_Budget_v_Actuals contains 7 records. two of those records are the original reords in the table with null values. I do not want these 2 records in the final output. Just the 5 inserted records.
February 16, 2009 at 4:24 am
Hey, no need to worry. I have managed to get the output I want using a delete and inner join statement combined. Many thanks for your help. You have helped me conceptualise exactly what I was trying to do which brought much need clarity. sqlservercentral does it again!!:)
Many Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply