Is there a way to take these two records and update the NULL values in one to match the filled in values for another? e.g.
Record One in CallTable: ID# 5263 CallID = 563522 Fname = Ben Lname = Foster
Record Two in CallTable: ID# 5357 CallID = 563522 Fname = NULL Lname = NULL
I need code that will basically
UPDATE CallTable SET Fname = value in non null record WHERE CallID = 563522 and Fname IS NULL
and UPDATE CallTable SET Lname = value in non null record WHERE CallID = 563522 and Lname IS NULL
I need to do this to thousands of records
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply