I have three columns I need to find wrong records ...
Rows with value x in third column should have all values C in second column.
How I can pull all those wrong together with right one C..as you see some of them are with A or B ..
Select *
into #temp
from (
Select 1 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 2 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 3 as Column1, 'B' as Column2,'x'as Column3
Union all
Select 4 as Column1, 'B' as Column2,'x' asColumn3
Union all
Select 5 as Column1, 'C' as Column2,'x'as Column3
Union all
Select 6 as Column1, 'D' as Column2,'y' as Column3
Union all
Select 7 as Column1, 'E' as Column2,'z' as Column3
) k
select * from #temp
where Column2 <> 'C' and Column3 = 'x'
update #temp
set Column2 = 'C'
where Column2 <> 'C' and Column3 = 'x'
I gave example of values in column .. but I do not know those values.
The values are not same in whole table ...
Also there are many records as x ..
Basically, column3 is account number but in column 2 i have some wrong data of person IDs.
For some reason person ID is created multiple times for same person, and same account is attached to it.
I have to find those rows.
Select *
into #temp
from (
Select 1 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 2 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 3 as Column1, 'B' as Column2,'x'as Column3
Union all
Select 4 as Column1, 'B' as Column2,'x' asColumn3
Union all
Select 5 as Column1, 'C' as Column2,'x'as Column3
Union all
Select 6 as Column1, 'D' as Column2,'y' as Column3
Union all
Select 7 as Column1, 'E' as Column2,'z' as Column3
) k
select *,ROW_NUMBER() over (partition by Column3 order by Column2)rn from #temp
Check these three columns ...Here is some data from my table.
I need only records like records where I have value 222 in third column.
As you see second column is messed. it should be same person ID as ll others have ...
For your example of 222 in column 3 there are four rows. Two with 915 in column 2, and one each with 99541 and 1128. Howe do you determine which of the three - 915, 99541, or 1128 is the correct value that should have been in column 2?
We will update manually that ...
I just need to find records like that ...
I need query which will pull records like those ..
5108 99541 222
5110 915 222
5114 915 222
5077 1128 222
Thank you JamesK for your replay.
Your query return only one row of Col3 for that particular value 222. I would need all 4 rows of that one, which is bad in my example. Also Col1 is important. That is unique key and it should help me to show all rows.
I made query, but query return also those they are good.
I need rows only where Col2 is messed ...
select Col1,Col2,Col3 from MyTable
where Col3 in
(select Col3 from MyTable group by Col3
having Count(Col3) > 1)
You can use the output of the query to pick all the rows that have the same col3
SELECT y1.*
FROM
YourTableNameHere AS y
INNER JOIN
(
SELECT
Col3
FROM
YourTable
GROUP BY
Col3
HAVING
MAX(CAST(Col2 AS VARCHAR(32))) <> MIN(CAST(Col2 AS VARCHAR(32)))
) y2 ON y2.Col3 = y1.Col3;
There must be something else beyond what you posted. In the query below, I am using the data you posted in your latest post and using it in the query. When you run the query, it returns nothing, which is what one would expect. You can copy this code to an SSMS window and run it.
CREATE TABLE #tmp(col1 INT, col2 INT, col3 NVARCHAR(32));
INSERT INTO #tmp VALUES
(6044,100070,'07182014'),
(6048,100070,'07182014'),
(6049,100070,'07182014'),
(6057,100070,'07182014'),
(6059,100070,'07182014'),
(6061,100070,'07182014')
SELECT y1.*
FROM
#tmp AS y1
INNER JOIN
(
SELECT
Col3
FROM
#tmp
GROUP BY
Col3
HAVING
MAX(CAST(Col2 AS NVARCHAR(32))) <> MIN(CAST(Col2 AS NVARCHAR(32)))
) y2 ON y2.Col3 = y1.Col3;
DROP TABLE #tmp;