Query help please. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query help please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-10-21 : 16:52:56

[code]Hi,

I need to query out all the loanid which has multiple same loanid only and update the
missing information on the row. Desire output is show below. I am using SQL 2008.

Thank you in advance.

IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO

CREATE TABLE #Temp
(
LoanId INT NULL,
TypeCd VARCHAR(15) NULL,
OrderDate DATETIME NULL
)
GO

INSERT #Temp VALUES (123, 'HC7', GETDATE()), (123, NULL, NULL), (456, 'ABC', getdate() ), (789, NULL, NULL), (789, 'HNO', GETDATE())
GO

SELECT *
FROM #Temp;
GO

LoanId TypeCd OrderDate
----------- --------------- -----------------------
123 HC7 2011-10-21 13:25:48.207
123 NULL NULL

789 NULL NULL
789 HNO 2011-10-21 13:25:48.207

Rules: Query out all the pairs LoanId and Update the infor that was missing.

-- Result want:

LoanId TypeCd OrderDate
----------- --------------- -----------------------
123 HC7 2011-10-21 13:25:48.207
123 HC7 2011-10-21 13:25:48.207

789 HNO 2011-10-21 13:25:48.207
789 HNO 2011-10-21 13:25:48.207

-- Testing...

SELECT t1.*, t2.*
FROM #Temp AS t1
JOIN #Temp AS t2
ON t1.LoanId = t2.LoanId
go[/code]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-21 : 19:27:31

First, why do you want to have identical data entries? Is there some part of the puzzle that you are not sharing with us?

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 04:30:37

is there a chance that there would be more than one set of records for same LoadId with NOT NULL values. IF yes, which values should you require to be update in your record with NULL values?

As per current sample data, below is sufficient though


UPDATE t
SET t.TypeCd = t1.TypeCd ,
t.OrderDate = t1.OrderDate 
FROM #temp t
CROSS APPLY (SELECT TOP 1 TypeCd,OrderDate
             FROM #temp
             WHERE TypeCd IS NOT NULL
               AND OrderDate IS NOT NULL
             ORDER BY NEWID())t1
WHERE t.TypeCd IS NULL
OR t.OrderDate IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources