Update and remove duplicates rows

Question

Post reply

Update and remove duplicates rows

Sagar Sawant

SSC Eights!

Points: 853
More actions
March 8, 2012 at 3:06 am

#258365

Hi,
Table "tbl_Reservation" contains below rows.
pk_ResId str_tbls ReservationId
1 19075,19078 1677
2 19067 1678
3 19081,19078 1683
4 19068 1678
5 19075,19068,19067 1679
6 19079,19068 1680
Now, I have to remove only those records from "str_Number" column in which any record
matches with the any row. e.g. 19078 number present in row number 3 like that...
So mine table result should be as follow:
pk_ResId str_Number ReservationId
1 19075,19078 1677
2 19067 1678
3 19081 1683
4 19068 1678
6 19079 1680
Any help would be greatly appreciated.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Could you please follow directions described in "The One":

http://www.sqlservercentral.com/articles/Best+Practices/61537/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sagar Sawant SSC Eights! Points: 853 More actions · Answer 2

Hi,

Thanks for the reply.

So I will post again and here is:

Table "tbl_Reservation" contains below rows.

CREATE TABLE #tbl_Reservation

(

pk_ResId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

str_tbls NVARCHAR(500),

ReservationId INT

)

INSERT INTO #mytable (str_tbls, ReservationId)

SELECT '19075,19078',1677 UNION ALL

SELECT '19067',1678 UNION ALL

SELECT '19081,19078',1683 UNION ALL

SELECT '19068',1678 UNION ALL

SELECT '19075,19068,19067',1679 UNION ALL

SELECT '19079,19068',1680

Now, I have to remove only those records from "str_tbls" column in which any record

matches with the any row. e.g. 19078 number present in row number 3 like that...

So mine table result should be as follow:

pk_ResId str_Number ReservationId

1 19075,19078 1677

2 19067 1678

3 19081 1683

4 19068 1678

6 19079 1680

Any help would be greatly appreciated.

Thanks in advance.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Sagar Sawant (3/8/2012)
Hi,
Thanks for the reply.
So I will post again and here is:
Table "tbl_Reservation" contains below rows.
CREATE TABLE #tbl_Reservation
(
pk_ResId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
str_tbls NVARCHAR(500),
ReservationId INT
)
INSERT INTO #mytable (str_tbls, ReservationId)
SELECT '19075,19078',1677 UNION ALL
SELECT '19067',1678 UNION ALL
SELECT '19081,19078',1683 UNION ALL
SELECT '19068',1678 UNION ALL
SELECT '19075,19068,19067',1679 UNION ALL
SELECT '19079,19068',1680
Now, I have to remove only those records from "str_tbls" column in which any record
matches with the any row. e.g. 19078 number present in row number 3 like that...
So mine table result should be as follow:
pk_ResId str_Number ReservationId
1 19075,19078 1677
2 19067 1678
3 19081 1683
4 19068 1678
6 19079 1680
Any help would be greatly appreciated.
Thanks in advance.

Try you code before posting!

1. You defined table #tbl_Reservation but inserted into #mytable

2. "I have to remove only those records from "str_tbls" column "

You CAN NOT remove records from column, cause there are none!

You can remove records from table or clear value in a column. What exactly you want to do?

3. In your setup record with PK=3 is: '19081,19078',1683

but in your expected results you want to see it as 19081, 1683. Am I right to understand that you want to update to remove value of 19078?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sagar Sawant SSC Eights! Points: 853 More actions · Answer 4

Thanks for the reply.

About your comment,

3. In your setup record with PK=3 is: '19081,19078',1683

but in your expected results you want to see it as 19081, 1683. Am I right to understand that you want to update to remove value of 19078?

Right, 19078 number will remove from that row and also other duplicate tbl number in coming rows.

Thanks.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

In order to achieve your requirement you will need to split the comma separated values.

Please read the following and create this very useful udf as per article:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

There is no viable solution without splitting the value first!

No here we are:

CREATE TABLE #tbl_Reservation

(

pk_ResId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

str_tbls NVARCHAR(500),

ReservationId INT

)

INSERT INTO #tbl_Reservation (str_tbls, ReservationId)

SELECT '19075,19078',1677 UNION ALL

SELECT '19067',1678 UNION ALL

SELECT '19081,19078',1683 UNION ALL

SELECT '19068',1678 UNION ALL

SELECT '19075,19068,19067',1679 UNION ALL

SELECT '19079,19068',1680

-- you need to split values first!

select t.pk_ResId

,d.Item AS str_tbls_sngl

-- str_tbls value occurance:

,ROW_NUMBER() OVER (PARTITION BY d.Item ORDER BY t.pk_ResId) RN

into #splitted

from #tbl_Reservation t

cross apply dbo.DelimitedSplit8K(str_tbls, ',') d

--dedupe values

delete #splitted where RN > 1

-- concatinate str_tbls values back to one comma separted string)

-- In SQL2008 you could use CTE and sinlge MERGE, in SQL2005,

-- you will need to use intermidiate temptable, but it you may

-- have a better performance with this anyway...

select s1.pk_ResId,

STUFF((SELECT ',' + s2.str_tbls_sngl

FROM #splitted s2

WHERE s2.pk_ResId=s1.pk_ResId

FOR XML PATH('')),1,1,'') AS str_tbls

into #wantedResults

from #splitted s1

group by s1.pk_ResId

-- delete records not in wantedResults

DELETE #tbl_Reservation

WHERE NOT EXISTS(SELECT 1 FROM #wantedResults wr WHERE wr.pk_ResId = #tbl_Reservation.pk_ResId)

-- update records as per wantedResults

UPDATE r

SET r.str_tbls = wr.str_tbls

FROM #tbl_Reservation r

JOIN #wantedResults wr

ON wr.pk_ResId = r.pk_ResId

-- job done

select * from #tbl_Reservation

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]