May 4, 2010 at 7:33 am
Here's a simple example of the scenario I'm trying to address:
T1
IDField1Field2Field3Field4
10000
T2
IDT1IDField1Field2Field3Field4
111000
212100
310310
410041
AFTER UPDATE
T1
IDField1Field2Field3Field4
11111
How can I do this using, ideally, a single update? I know how I could do it by looping iteratively through each record of T2, but I'd rather not. I tried an update with a JOIN, but all that did was made it so that only the first record of T2 was processed, and the rest were ignored.
The way the update should essentially function is that it would start with the first record in T2, compare it with the corresponding fields in T1, and update the T1 record if and only if the record in T1 is 0.
May 4, 2010 at 7:55 am
I think I almost understood what you mean, but it would be clearer with some more information:
1) Table scripts
2) Sample data
3) Expected output
4) What you tried so far
See the article linked in my signature for more information.
-- Gianluca Sartori
May 4, 2010 at 8:03 am
here you go:
DECLARE @test-2 TABLE
(
ID INT,
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)
DECLARE @Test2 TABLE
(
ID INT,
T1ID INT,
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)
INSERT INTO @test-2
VALUES (1, 0, 0, 0, 0)
INSERT INTO @Test2
VALUES (1, 1, 1, 0, 0, 0)
INSERT INTO @Test2
VALUES (2, 1, 2, 1, 0, 0)
INSERT INTO @Test2
VALUES (3, 1, 0, 3, 1, 0)
INSERT INTO @Test2
VALUES (4, 1, 0, 0, 4, 1)
UPDATE t1
SET t1.Field1 = (CASE WHEN t1.Field1 = 0 THEN t2.Field1 ELSE t1.Field1 END),
t1.Field2 = (CASE WHEN t1.Field2 = 0 THEN t2.Field2 ELSE t1.Field2 END),
t1.Field3 = (CASE WHEN t1.Field3 = 0 THEN t2.Field3 ELSE t1.Field3 END),
t1.Field4 = (CASE WHEN t1.Field4 = 0 THEN t2.Field4 ELSE t1.Field4 END)
FROM @test-2 t1
JOIN @Test2 t2 ON t2.T1ID = t1.ID
SELECT * FROM @test-2
OUTPUT :
IDField1Field2Field3Field4
11000
DESIRED OUTPUT :
IDField1Field2Field3Field4
11111
May 4, 2010 at 8:33 am
This should do the trick for you:
UPDATE @test-2
SET t1.Field1 = (CASE WHEN t1.Field1 = 0 THEN t2.Field1 ELSE t1.Field1 END),
t1.Field2 = (CASE WHEN t1.Field2 = 0 THEN t2.Field2 ELSE t1.Field2 END),
t1.Field3 = (CASE WHEN t1.Field3 = 0 THEN t2.Field3 ELSE t1.Field3 END),
t1.Field4 = (CASE WHEN t1.Field4 = 0 THEN t2.Field4 ELSE t1.Field4 END)
FROM @test-2 t1
INNER JOIN (
SELECT T1ID,
Field1 = MIN(NULLIF(Field1,0)),
Field2 = MIN(NULLIF(Field2,0)),
Field3 = MIN(NULLIF(Field3,0)),
Field4 = MIN(NULLIF(Field4,0))
FROM @Test2
GROUP BY T1ID
) AS t2
ON t2.T1ID = t1.ID
-- Gianluca Sartori
May 4, 2010 at 8:45 am
Ah, sorry, i guess the example data I gave was a bit misleading - it's not that I want the minimum values - rather, what I want is the *first* value. In other words, if I were to be doing this through an iterative approach, when the first record in T2 is encountered, T1 would be updated with Field1 set to 1. The next record would not change T1 field1, because T1 Field1 is no longer 0, however, since field2 is still 0, it would update it to 1, which is what that record's Field2 value is.
May 4, 2010 at 10:47 am
That's ok, I think I got it:
UPDATE @test-2
SET Field1 = (SELECT TOP 1 Field1 FROM @Test2 WHERE T1Id = T1.Id AND Field1 != 0 ORDER BY Id),
Field2 = (SELECT TOP 1 Field2 FROM @Test2 WHERE T1Id = T1.Id AND Field2 != 0 ORDER BY Id),
Field3 = (SELECT TOP 1 Field3 FROM @Test2 WHERE T1Id = T1.Id AND Field3 != 0 ORDER BY Id),
Field4 = (SELECT TOP 1 Field4 FROM @Test2 WHERE T1Id = T1.Id AND Field4 != 0 ORDER BY Id)
FROM @test-2 AS T1
I can't find any method other than four subqueries. Hope this works for you.
-- Gianluca Sartori
May 4, 2010 at 10:59 am
Yeah I was considering that approach, unfortunately the simple example that I gave is much less involved than the actual problem. Was really hoping for some better way of doing it :/
May 4, 2010 at 11:45 am
There quite possibly is, but I'm still not quite clear on what you're doing.
What are your expected results from this:
DECLARE @test-2 TABLE
(
ID INT,
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)
DECLARE @Test2 TABLE
(
ID INT,
T1ID INT,
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)
INSERT INTO @test-2
VALUES (1, 0, 0, 0, 0)
INSERT INTO @test-2
VALUES (2, 0, 2, 0, 0)
INSERT INTO @Test2
VALUES (1, 1, 1, 0, 0, 0)
INSERT INTO @Test2
VALUES (2, 1, 2, 1, 0, 0)
INSERT INTO @Test2
VALUES (3, 1, 0, 3, 1, 0)
INSERT INTO @Test2
VALUES (4, 1, 0, 0, 4, 1)
INSERT INTO @Test2
VALUES (5, 2, 2, 0, 0, 1)
INSERT INTO @Test2
VALUES (6, 2, 2, 1, 0, 0)
INSERT INTO @Test2
VALUES (7, 2, 0, 3, 4, 0)
INSERT INTO @Test2
VALUES (8, 2, 0, 0, 4, 2)
May 4, 2010 at 12:16 pm
T1
IDField1Field2Field3Field4
11111
22141
May 4, 2010 at 12:52 pm
kramaswamy (5/4/2010)
Was really hoping for some better way of doing it :/
I'm not sure what you are hoping for, but maybe this one will do:
DECLARE @test-2 TABLE
(
ID INT,
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)
DECLARE @Test2 TABLE
(
ID INT,
T1ID INT,
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)
INSERT INTO @test-2
VALUES (1, 0, 0, 0, 0)
INSERT INTO @test-2
VALUES (2, 0, 0, 0, 0)
INSERT INTO @Test2
VALUES (1, 1, 1, 0, 0, 0)
INSERT INTO @Test2
VALUES (2, 1, 2, 1, 0, 0)
INSERT INTO @Test2
VALUES (3, 1, 0, 3, 1, 0)
INSERT INTO @Test2
VALUES (4, 1, 0, 0, 4, 1)
INSERT INTO @Test2
VALUES (1, 2, 2, 0, 3, 0)
INSERT INTO @Test2
VALUES (2, 2, 1, 4, 0, 0)
INSERT INTO @Test2
VALUES (3, 2, 0, 3, 5, 0)
INSERT INTO @Test2
VALUES (4, 2, 0, 0, 4, 1)
SELECT
*
FROM
@Test2
;WITH
Filter AS
(
SELECT
T1ID,
-- Combine ID + Fieldx into a BIGINT
-- Make it NULL if Fieldx is 0
-- Determine the minimum of this combined value
-- Extract Fieldx value from the minimum
CAST(MIN(CAST(2147483648 AS BIGINT) * ID + NULLIF(Field1, 0)) % CAST(2147483648 AS BIGINT) AS INT) V1,
CAST(MIN(CAST(2147483648 AS BIGINT) * ID + NULLIF(Field2, 0)) % CAST(2147483648 AS BIGINT) AS INT) V2,
CAST(MIN(CAST(2147483648 AS BIGINT) * ID + NULLIF(Field3, 0)) % CAST(2147483648 AS BIGINT) AS INT) V3,
CAST(MIN(CAST(2147483648 AS BIGINT) * ID + NULLIF(Field4, 0)) % CAST(2147483648 AS BIGINT) AS INT) V4
FROM
@Test2
GROUP BY
T1ID
)
UPDATE
T
SET
Field1 = COALESCE(F.V1, Field1),
Field2 = COALESCE(F.V2, Field2),
Field3 = COALESCE(F.V3, Field3),
Field4 = COALESCE(F.V4, Field3)
FROM
@test-2 T
JOIN
Filter F ON F.T1ID = T.ID
SELECT
*
FROM
Peter
Edit: Added COALESCE to deal with all 0's, added some comments
Edit: Added some explicit CAST's to get rid of implicit conversions to NUMERIC
May 4, 2010 at 1:22 pm
Peter Brinkhaus (5/4/2010)
kramaswamy (5/4/2010)
Was really hoping for some better way of doing it :/I'm not sure what you are hoping for, but maybe this one will do:
Very nice. I'm using a very similar method in an attempt to solve part of one of the most recent T-SQL Challenges :hehe:. Haven't quite gotten that one totally figured out yet tho.
Kramaswamy,
To verify, why would the second line not be:
2 2 2 4 1
@test-2 started with a 2 in the third column, was it intentional that you replaced it with a 1?
Easy enough to fix with another NULLIF in the coalesce if that was an oversight, just wanted to clarify.
May 4, 2010 at 1:25 pm
Hey Kumar, based on the sample data and your requirement, i have arrived half-the-way.
This will not produce the full result, but i have come to a point where i have identified the first non-zero value of each column. As it is an hour past midnight, i will continue on this tomorrow. Till then, some kind souls here will continue from this.
Catch u tomo morning!
~EDIT : REMOVED the half-baked code. The final code is present in the next post of mine in the thread!
May 4, 2010 at 1:56 pm
Hey i might have got what you wanted. Here is the full code:
First the test environment; sample data and the schemas
Note: I have used the same schema provided by you and sample by Seth.
DECLARE @test-2 TABLE
(
ID INT,
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)
DECLARE @Test2 TABLE
(
ID INT,
T1ID INT,
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)
INSERT INTO @test-2
VALUES (1, 0, 0, 0, 0)
INSERT INTO @test-2
VALUES (2, 0, 2, 0, 0)
INSERT INTO @Test2
VALUES (1, 1, 1, 0, 0, 0)
INSERT INTO @Test2
VALUES (2, 1, 2, 1, 0, 0)
INSERT INTO @Test2
VALUES (3, 1, 0, 3, 1, 0)
INSERT INTO @Test2
VALUES (4, 1, 0, 0, 4, 1)
INSERT INTO @Test2
VALUES (5, 2, 2, 0, 0, 1)
INSERT INTO @Test2
VALUES (6, 2, 2, 1, 0, 0)
INSERT INTO @Test2
VALUES (7, 2, 0, 3, 4, 0)
INSERT INTO @Test2
VALUES (8, 2, 0, 0, 4, 2)
Now for the code that you wanted. This will update all the fields in @test-2 table with the corresponding first non-zero value from @Test2, for each ID in @test. Here is the code:
;WITH CTE
AS
(
SELECT T1ID ,
ROW_NUMBER() OVER(PARTITION BY T1ID ORDER BY T1ID) ROW_NUM,
Field1 ,
Field2 ,
Field3 ,
Field4
FROM @Test2
),
GROUPED_DATA
AS
(
SELECT T1ID, MIN(ROW_NUM) ROW_NUM , 1 AS FIELD FROM CTE WHERE FIELD1 <> 0
GROUP BY T1ID
UNION ALL
SELECT T1ID, MIN(ROW_NUM) ROW_NUM , 2 AS FIELD FROM CTE WHERE FIELD2 <> 0
GROUP BY T1ID
UNION ALL
SELECT T1ID, MIN(ROW_NUM) ROW_NUM , 3 AS FIELD FROM CTE WHERE FIELD3 <> 0
GROUP BY T1ID
UNION ALL
SELECT T1ID, MIN(ROW_NUM) ROW_NUM , 4 AS FIELD FROM CTE WHERE FIELD4 <> 0
GROUP BY T1ID
),
DATA AS
(
SELECT T.T1ID,
G.FIELD,
CASE
WHEN G.FIELD = 1 THEN T.FIELD1
WHEN G.FIELD = 2 THEN T.FIELD2
WHEN G.FIELD = 3 THEN T.FIELD3
WHEN G.FIELD = 4 THEN T.FIELD4
END VALUE
FROM GROUPED_DATA G
INNER JOIN CTE T
ON (G.T1ID = T.T1ID AND G.ROW_NUM = T.ROW_NUM )
),
FINAL AS
(
SELECT T1ID,[1] 'Field1',[2] 'Field2',[3] 'Field3',[4] 'Field4' FROM
(SELECT * FROM DATA) PIVOT_TABLE
PIVOT
(MAX(VALUE) FOR FIELD IN ([1],[2],[3],[4])) PIVOT_HANDLE
)
UPDATE TEST
SET
TEST.Field1 = F.Field1,
TEST.Field2 = F.Field2,
TEST.Field3 = F.Field3,
TEST.Field4 = F.Field4
FROM FINAL F
INNER JOIN @test-2 TEST
ON TEST.ID = F.T1ID
Please tell us back here if that's what you wanted!! Hope this helps!!
Cheers!
May 4, 2010 at 2:01 pm
When you run my code, i get these values in @test-2 table before and after the UPDATE.
SELECT * FROM @test-2
1. BEFORE Update
ID Field1 Field2 Field3 Field4
----------- ----------- ----------- ----------- -----------
1 0 0 0 0
2 0 2 0 0
2.AFTER Update
ID Field1 Field2 Field3 Field4
----------- ----------- ----------- ----------- -----------
1 1 1 1 1
2 2 1 4 1
This is what you have posted as your desired result, am i right?
May 4, 2010 at 2:25 pm
Nice code, Mr. Coffee!
Looks like you specialized in pivot/unpivot lately...
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply