January 29, 2013 at 7:35 am
Dear all,
I have a table which has duplicate records and I would like to update the records by finding its duplicate in the table.
I trying to execute the following logic in the sq server:
input example:
name, cusip, isin
a1,345,
b1,445, ,
c1, ,67887
d1, ,5666
e1, 35544, ,
a1, ,35678
b1
a1
desired output
name, cusip, isin
a1,345,
b1,445, ,
c1, ,67887
d1, ,5666
e1, 35544, ,
a1, ,35678
b1, 455, ,
a1, 345, ,
I have implemented the following query for the above scenario but I can not get it work, as the output is showing a blank table.
SELECT a.name,isnull(a.cusip,b.isin)
from [dbo].[BWIC] a
join (select distinct name,isin, cusip
from [dbo].[BWIC]
where isin is not null
and cusip is not null) b
on a.name = b.name
Any help would be very much appreciated.
Thanks
January 29, 2013 at 8:01 am
Your requirement is not clear. How do you determine which value should you enter?
You should post DDL and sample data in a consumable format so we don't waste time on it. This time I did it for you and I hope you do it for us on the next posts.
Here are 2 options that might fit your necessities. Both return different results.
CREATE TABLE BWIC(
namechar(2) NOT NULL,
cusipintNULL,
isinint NULL)
INSERT INTO BWIC VALUES
('a1',345,NULL),
('b1',445,NULL),
('c1',NULL,67887),
('d1',NULL,5666),
('e1',35544,NULL),
('a1',NULL,35678),
('b1',NULL,NULL),
('a1',NULL,NULL)
SELECT a.name,COALESCE(a.cusip,a.isin,b.cusip,b.isin)
ISNULL(a.isin,b.isin) END isin
from [dbo].[BWIC] a
LEFT
join (select distinct name, MIN( isin) isin, MIN(cusip) cusip
from [dbo].[BWIC]
where isin is not null
OR cusip is not null
GROUP BY name) b
on a.name = b.name
AND a.cusip IS NULL
AND a.isin IS NULL
SELECT a.name,ISNULL(a.cusip,b.cusip) cusip, CASE WHEN ISNULL(a.cusip,b.cusip) IS NULL THEN ISNULL(a.isin,b.isin) END isin
from [dbo].[BWIC] a
LEFT
join (select distinct name, MIN( isin) isin, MIN(cusip) cusip
from [dbo].[BWIC]
where isin is not null
OR cusip is not null
GROUP BY name) b
on a.name = b.name
AND a.cusip IS NULL
AND a.isin IS NULL
February 1, 2013 at 4:55 am
Dear Luis Cazares,
Apology for the late response. Thank you so much for your quick response and help. Apology for the lack of DDL, I will keep that in mind next time.
I am very grateful to your solution and help. Thank you so much.
Have a great day or evening.
Kind regards
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply