I have a table that looks like this:
ID AMID DESC value
-----------------------------------------------------------------
100 type A AMID type A 10
101 type B AMID type B 18
102 type C AMID type C 34
101 null null 4
102 null null 19
103 type D AMID type D 6
103 type E null 7
The table contains around 6 million rows.
Now I want to have the result like this
ID AMID DESC value
-------------------------------------------------------------
100 type A AMID type A 10
101 type B AMID type B 18
102 type C AMID type C 34
101 type B AMID type B 4
102 type C AMID type C 19
103 type D AMID type D 6
103 type E null 7
It has show the same values when the AMID for ID in both rows are equal are one of it is null, if the AMID is different then leave it like that..
Thanks in advance for the help..
Cheers,
Harish
First, let's set up some proper DDL and sample data:
USE tempdb;
GO
CREATE TABLE dbo.AMID
(
ID INT,
AMID VARCHAR(32),
[DESC] VARCHAR(32),
value INT
);
INSERT dbo.AMID SELECT 100, 'type A', 'AMID type A', 10
UNION ALL SELECT 101, 'type B', 'AMID type B', 18
UNION ALL SELECT 102, 'type C', 'AMID type C', 34
UNION ALL SELECT 101, null , null , 4
UNION ALL SELECT 102, null , null , 19
UNION ALL SELECT 103, 'type D', 'AMID type D', 6
UNION ALL SELECT 103, 'type E', null , 7;
Instead of using TOP without ORDER BY, which I don't even know what it means, you can pick exactly one row to use for populating the NULL rows using partition + order. In your limited sample data you only have a max of two rows for any ID, but in case you can have more, this is important (otherwise, like TOP without ORDER BY, you're going to pull some arbitrary row, making your update less than predictable). This orders by the value in the AMID column, but you can change this to pick the top row per ID using any criteria in the table.
;WITH src AS
(
SELECT ID, AMID, [DESC]
FROM dbo.AMID
WHERE AMID IS NULL AND [DESC] IS NULL
),
nv AS
(
SELECT ID, AMID, [DESC], rn = ROW_NUMBER() OVER
(PARTITION BY ID ORDER BY AMID) -- change this ordering accordingly
FROM dbo.AMID
WHERE AMID IS NOT NULL
)
UPDATE src
SET AMID = nv.AMID, [DESC] = COALESCE(nv.[DESC], src.[DESC])
FROM src INNER JOIN nv
ON src.ID = nv.ID
WHERE nv.rn = 1;
SELECT ID, AMID, [DESC], Value FROM dbo.AMID;
Results:
ID AMID DESC value
--- ------ ----------- -----
100 type A AMID type A 10
101 type B AMID type B 18
102 type C AMID type C 34
101 type B AMID type B 4
102 type C AMID type C 19
103 type D AMID type D 6
103 type E NULL 7
Don't forget to clean up:
DROP TABLE dbo.AMID;
GO
As an aside, DESC is a horrible name for a column, since it's a T-SQL keyword, and therefore always has to be escaped with [double quotes].
If i've understood your requirement correctly:
SELECT ID
, COALESCE(AMID, (
SELECT TOP 1 AMID FROM Table t2 WHERE t2.ID=ID AND t2.AMID IS NOT NULL
))AS AMID
, DESC
, value
FROM Table
COALESCE (Transact-SQL)
select a1.ID,
ISNULL(a1.amid, a3.amid),
a1.[DESC],
a1.value
FROM amid a1
LEFT OUTER JOIN (select a2.id, amid = max(a2.amid)
from amid a2
where a2.amid is not null
group by a2.id) a3 on a3.id = a1.id
Not clear from your question what version of SQL you're using but the above should work on anything from SQL2000 up.
Basically you can tweak the query in the derived table to get the results you prefer.
