Skip to main content

Select duplicated records in a table and match with another table 

SELECT ID,NAME,COUNT(*) FROM TABLE_NAME WHERE ID IN ( SELECT ID_2 FROM TABLE_NAME) GROUP BY ID,NAME HAVING COUNT(*) >1;

Comments

Post a Comment

Popular posts from this blog

SQL Blocking query

SELECT s.session_id, r.status, r.blocking_session_id 'Blk by' , r.wait_type, wait_resource, r.wait_time / ( 1000.0 ) 'Wait Sec' , r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / ( 1000.0 ) 'Elaps Sec' , Substring (st. TEXT ,(r.statement_start_offset / 2 ) + 1 , (( CASE r.statement_end_offset WHEN - 1 THEN Datalength (st. TEXT ) ELSE r.statement_end_offset END - r.statement_start_offset) / 2 ) + 1 ) AS statement_text, Coalesce ( Quotename ( Db_name (st.dbid)) + N '.' + Quotename (Object_sch...
Post a Comment
Read more

Split String which has space and replace spaces with "/"

Sample String "ABCD123 ETS, NM" UPDATE TABLE_NAME SET COLUMN_NAME = SUBSTR( 'ABCD123 ETS, NM' , 0 , 4 ) || REPLACE ((SUBSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , '/' )), 5 ,INSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , ',/' )), ',' , 1 ) - 4 )), ' ' , '/' ) || REPLACE (SUBSTR( 'ABCD123 ETS, NM' ,INSTR( 'ABCD123 ETS, NM' , ',' , 1 ) + 2 ), 'NM' , 'NEW MEXICO' ) WHERE CONDITIONS;
Post a Comment
Read more