August 19, 2005 at 8:15 am
i have two tables with that can be joined together on a single column key ,, with 1 to many relationship
table f has columns x1,x2,x3,x4 and table d has y1,y2,y3,y4 -- both integers
i want to move data to table f but dont want to overwrite any value in x1-x4 ,, most of the values are same between x1-4 and y1-4 but some rows have some y values not in table f (x1-4)
so .... move y1-y4 to x1-4 IF that value does not exist in x1-x4 e.g.
x1 x2 x3 x4 y1 y2 y3 y4
1 2 1 2 3
will become
x1 x2 x3 x4
1 2 3
if x1-x4 is already occupied then we dont do anything. so when joing these tables we can ignore records in table f if none of the slots are empty in table f x1-4
thanks for your prompt input
August 19, 2005 at 8:26 am
can you post the actual table definitions and soe sampla data along with the expected results and exceptions (if any).
August 19, 2005 at 8:35 am
table F has
key varchar(12)
x1 int, x2 int, x3 int, x4 int
Sample Data
key x1 x2 x3 x4
1 12 1 2 null
2 1 2 null null
12 2 null null null
14 null null null null
Table D has
key varchar(12)
y1 int, y2 int, y3 int, y4 int
Sample Data
key y1 y2 y3 y4
1 12 1 2 null
2 1 2 8 null
12 null null null null
14 1 null null null
after the update tabel F look like this
key x1 x2 x3 x4
1 12 1 2 null
2 1 2 8 null
12 2 null null null
14 1 null null null
i hope it helps,,
August 19, 2005 at 8:49 am
SET NOCOUNT ON
DECLARE @F TABLE
(
[Key] INT NULL,
X1 INT NULL,
X2 INT NULL,
X3 INT NULL,
X4 INT NULL
)
INSERT @F
SELECT 1, 12, 1, 2, null UNION ALL
SELECT 2, 1, 2, null, null UNION ALL
SELECT 12, 2, null, null, null UNION ALL
SELECT 14, null, null, null, null
DECLARE @D TABLE
(
[Key] INT NULL,
Y1 INT NULL,
Y2 INT NULL,
Y3 INT NULL,
Y4 INT NULL
)
INSERT @D
SELECT 1, 12, 1, 2, null UNION ALL
SELECT 2, 1, 2, 8, null UNION ALL
SELECT 12, null, null, null, null UNION ALL
SELECT 14, 1, null, null, null
UPDATE F
SET
X1 = COALESCE(X1, Y1),
X2 = COALESCE(X2, Y2),
X3 = COALESCE(X3, Y3),
X4 = COALESCE(X4, Y4)
FROM
@F F
JOIN
@D D
ON
F.[Key] = D.[Key]
SELECT * FROM @F
Regards,
gova
August 19, 2005 at 9:18 am
with 1 to many relationship
according to this you might have :
tblx
key x1 x2 x3 x4
1 1 null null null
and tbly
Fkey y1 y2 y3 y4
1 1 1 null null
1 1 2 null null
wich value you want in tblX from y2 tbly? 1 or 2
Vasc
August 19, 2005 at 9:28 am
i intentionaly did not get into 1-many problem ,, but there is another column in tableY called visitnumber
so using your tableY it will look like this
Fkey y1 y2 y3 y4 visitnumber
1 1 1 null null 1
1 1 2 null null 2
in this case i want the highest number visitnumber that has any non-null values in y1-4 i.e. 2nd record in the above table
thanks for you reply
August 19, 2005 at 9:48 am
SET NOCOUNT ON
DECLARE @F TABLE
(
[Key] INT NULL,
X1 INT NULL,
X2 INT NULL,
X3 INT NULL,
X4 INT NULL,
visitnumber INT NULL
)
INSERT @F
SELECT 1, 12, 1, 2, null,null UNION ALL
SELECT 2, 1, 2, null, null,0 UNION ALL
SELECT 12, 2, null, null, null,null UNION ALL
SELECT 14, null, null, null, null,1
DECLARE @D TABLE
(
[Key] INT NULL,
Y1 INT NULL,
Y2 INT NULL,
Y3 INT NULL,
Y4 INT NULL,
visitnumber INT NULL
)
INSERT @D
SELECT 1, 12, 1, 2, null,null UNION ALL
SELECT 2, 1, 2, 8, null,4 UNION ALL
SELECT 12, null, 3, null, null,7 UNION ALL
SELECT 12, 1, null, null, null,6 UNION ALL
SELECT 14,null,null,null,null,8
UPDATE F
SET
X1 = COALESCE(X1, Y1),
X2 = COALESCE(X2, Y2),
X3 = COALESCE(X3, Y3),
X4 = COALESCE(X4, Y4),
f.visitnumber=case when isnull(f.visitnumber,0)>isnull(e.visitnumber,0) then isnull(f.visitnumber,0) else isnull(e.visitnumber,0) end
FROM
@F F
INNER JOIN
@D D
ON
F.[Key] = D.[Key]
INNER JOIN
(select ,max(isnull(visitnumber,0)) visitnumber from @D where coalesce(y1,y2,y3,y4) is not null group by ) e
on f.=e.
select * from @f
Vasc
August 19, 2005 at 10:17 am
there is one problem with this ,, look at this case
INSERT @F
SELECT 1, 12, 1, 2, null,null UNION ALL
INSERT @D
SELECT 1, 9, 1, 2, null,null UNION ALL
do we not lose 9 in this case?
we would get
1 12 1 2 null null
but we should get
1 12 1 2 9 null
make sense?
August 19, 2005 at 10:25 am
one thing i mentioned in my initial post was that --- i cant overwrite any data in table F ,, so if X1 is not null then i dont want to do anything with it,, same is true for X2-X4 ,, if all are non null then nothing need to be updated , regardless of any differing values in table D ,, thats the part which makes it tricky i think,, thanks
August 19, 2005 at 10:26 am
MAYBE I didn't get the specs correctly ...
but can you describe why you should get
1 12 1 2 9 null ???
X3 IS NULL in dest rec
from Source rec Y3 is NULL but Y1 is 9 so you are
replacing X3 with Y1?
Vasc
August 19, 2005 at 10:36 am
ok,, let me restate the issue
table F has most recent and correct data and can not be overwritten with values from table D -- most of the these records have same values BUT in case there is some value for any given record in D that is NOT in table F i want it moved to F PROVIDED
1: there is room for it i.e. there is some NULL value in X1-X4
2: keep values in X1-X4 as is AND tag the new value in the first NULL slot in X1-X4
so if u have 1 2 3 4 in D and 9 4 3 NULL in F then result will be 9 4 3 1 -- as i only have one open slot to use even though 2 is also a candidate .. i can just use one and it can be any value between 1 & 2
if i have 2 null null null in F and 3 2 1 null in D then update would make it
2 3 1 null in F ,, again i am leaving 2 in F as is and filling the rest of the slots
I hope it makes sense?
August 19, 2005 at 11:16 am
Vasc can give an elegent solution. I guess this will solve the problem.
SET NOCOUNT ON
DECLARE @F TABLE
(
[Key] INT NULL,
X1 INT NULL,
X2 INT NULL,
X3 INT NULL,
X4 INT NULL
)
INSERT @F
SELECT 1, 12, 1, 2, null UNION ALL
SELECT 2, 1, 2, null, null UNION ALL
SELECT 12, 2, null, null, null UNION ALL
SELECT 14, null, null, null, null
DECLARE @D TABLE
(
[Key] INT NULL,
Y1 INT NULL,
Y2 INT NULL,
Y3 INT NULL,
Y4 INT NULL,
VisitNumber INT NULL
)
INSERT @D
SELECT 1, 12, 1, 2, null, 1 UNION ALL
SELECT 2, 1, 2, 9, null, 2 UNION ALL
SELECT 2, 1, 2, 8, null, 1 UNION ALL
SELECT 12, null, null, null, null, null UNION ALL
SELECT 14, 1, null, null, null, null
UPDATE F
SET
X1 = COALESCE(X1, Y1),
X2 = COALESCE(X2, Y2),
X3 = COALESCE(X3, Y3),
X4 = COALESCE(X4, Y4)
FROM
@F F
JOIN
(
SELECT [Key], MAX(Y1) Y1, MAX(Y2) Y2, MAX(Y3) Y3, MAX(Y4) Y4
FROM
@D
WHERE
[Key] IN (SELECT [Key] FROM @D WHERE VisitNumber IS NULL)
GROUP BY [Key]
UNION
SELECT DM.[Key], Y1, Y2, Y3, Y4
FROM
@D DM
JOIN
(
SELECT [Key], MAX(VisitNumber) VisitNumber
FROM
@D
WHERE
[Key] NOT IN (SELECT [Key] FROM @D WHERE VisitNumber IS NULL)
GROUP BY [Key]) DMax
ON
DM.[Key] = DMax.[Key] AND
DM.VisitNumber = DMax.VisitNumber) D
ON
F.[Key] = D.[Key]
SELECT * FROM @F
Regards,
gova
August 19, 2005 at 11:27 am
this works fine except the case i pointed out earlier ,, use these two as i have in your solution and c what i mean
INSERT @F
SELECT 1, 12, 1, 2, null UNION ALL
SELECT 2, 1, 2, null, null UNION ALL
SELECT 12, 2, null, null, null UNION ALL
SELECT 14, null, null, null, null
INSERT @D
SELECT 1, 9, 1, 2, null, 1 UNION ALL
SELECT 2, 1, 2, 9, null, 2 UNION ALL
SELECT 2, 1, 2, 8, null, 1 UNION ALL
SELECT 12, null, null, null, null, null UNION ALL
SELECT 14, 1, null, null, null, null
check the values in @F after the update ,, where did the 9 go for key 1?
August 19, 2005 at 11:34 am
You said
table F has most recent and correct data and can not be overwritten with values from table D
So In Table F key 1 has 12 and Table D it is 9. The query retains the value 12 in Table F so 9 is gone. Can you explain why 9 is to be retained here.
Regards,
gova
August 19, 2005 at 11:46 am
these values are patient diagnosis codes i.e upto 4 of em ,, order is irrelevant ,, so x1 is not related to y1 or y2 etc.. these are just diagosis codes ,, table F has most recect numbers but i would like to add any previous code if found missing based of the values in D
if a person has diagnoses codes of 12,1,20,null in table F and codes of 23,1,12,4 in table D ,, it simpley means i have some diagnosis codes in table D that are NOT in table F , so i want to add that missing code(s) to table F , and i want to add it to first empty slot in table F ,, making it 12,1,20,23 ,, now what u saying is 12 would stay but 23 will not -- may be i did not make it more clear ,, its the other way around i.e. 23 is not in F and it should be added,, 1 is in F so ignore it,, 12 is in F so ignore it,, 4 is not in F but after adding 23 there is no empty slot left so just ignore it,,
i hope its clear now? at this poing i will take any solution including a cursor ,, thanks so much
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply