SQL : Delete duplicate record based on the wild string

Question

Hi All - Below is my sample output from the query : The Key column is concat of 1_ or 4_ with 'Number' column. My question is if there is same value in the column 'Number' and the key column column starts with both 1 and 4 then i have to select those rows starting with '1_ 'and insert into a another history table.

For example : IDs from 1 to 10, Each row has same 'number' value and these start with either 1 and 4 then i have to move rows 1,3,5,7,9 into the history table starting with only '1_'.

To keep it simple : If the same number value in key column starting with 1 and 4 then only move the 1_ values to history table. Could you please help.

\

Answer 1

Hi @kkran Try this query:

INSERT INTO History_Table
SELECT A.* 
FROM Sample_table A JOIN Sample_table B ON A.NUMBER=B.NUMBER AND A.[KEY]<>B.[KEY]
WHERE LEFT(A.[KEY],2)='1_'

Best regards,

Cosmog Hong