March 2, 2021 at 6:31 pm
Yes, there is. If you want working code, you know the drill: please provide DDL and sample data in the form of UPDATE statements.
March 2, 2021 at 6:34 pm
My previous 'yes' answer does depend on there being a link (eg, PersonId) between all of the rows which are associated. If there is no link between the different rows, this problem potentially becomes impossible to solve.
March 2, 2021 at 6:36 pm
the only link is the CallID number
March 2, 2021 at 6:49 pm
CREATE TABLE [Reporting].[dbo].[D2_CallLog](
[ID] [Int] NOT NULL,
[Fname] [nvarchar](255) NULL,
[Lname] [nvarchar](255) NULL,
[CallID] [int] NULL,
CONSTRAINT [PK_D02CallLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
March 2, 2021 at 7:33 pm
Would a Self Join be in order OR something like this ?:
UPDATE [Reporting].[dbo].[D02_CallLog] A
SET A.[Fname] = (SELECT B.[Fname] FROM [Reporting].[dbo].[D02_CallLog] B)
WHERE A.[CallID] = B.[CallID] and A.[Fname] is null and B.[Fname] is not NULL
UPDATE a
SET a.Fname=b.Fname
FROM [dbo].[D2_CallLog] a
CROSS APPLY(SELECT TOP(1) *
FROM [dbo].[D2_CallLog] b
WHERE b.CallID = a.CallID
AND b.Fname IS NOT NULL) b
WHERE a.Fname IS NULL;
UPDATE a
SET a.Lname=b.Lname
FROM [dbo].[D2_CallLog] a
CROSS APPLY(SELECT TOP(1) *
FROM [dbo].[D2_CallLog] b
WHERE b.CallID = a.CallID
AND b.Lname IS NOT NULL) b
WHERE a.Lname IS NULL;
March 2, 2021 at 7:41 pm
UPDATE CT --<<-- this MUST be CT, NOT CallTable
SET Fname = ISNULL(Fname_Max, Fname), Lname = ISNULL(Lname_Max, Lname)
FROM CallTable CT
INNER JOIN (
SELECT CallID, MAX(Fname) AS Fname_Max, MAX(Lname) AS Lname_Max
FROM CallTable
WHERE Fname IS NOT NULL OR Lname IS NOT NULL
GROUP BY CallID
) AS CT_Max ON CT_Max.CallID = CT.CallID
WHERE CT.Fname IS NULL OR CT.Lname IS NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 2, 2021 at 7:46 pm
What about this?:
Update A Set A.[Fname] = B.[Fname]
from [Reporting].[dbo].[D02_CallLog] A
inner join [Reporting].[dbo].[D02_CallLog] B
on A.[CallID] = B.[CallID]
where A.[CallID] is not null and B.[Fname] is null
March 2, 2021 at 7:50 pm
What about this?:
Update A Set A.[Fname] = B.[Fname]
from [Reporting].[dbo].[D02_CallLog] A
inner join [Reporting].[dbo].[D02_CallLog] B
on A.[CallID] = B.[CallID]
where A.[CallID] is not null and B.[Fname] is null
Almost, but you are setting the non-NULL values to NULL in that query. You also have CallID in the last line where you meant to put Fname.
The last line needs to be:
where A.[Fname] is null and B.[Fname] is not null
It's not very clean code because there might be more than one row returned that is not null.
You are better off use Scott's method or my method with an ORDER BY in the CROSS APPLY, both of which will only return one row to update to.
March 2, 2021 at 7:58 pm
I was worried about potential performance issuing in the subquery methods, at least for larger numbers of rows.
For smaller numbers of rows, the subquery may in fact perform somewhat better.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply