2017-05-06

Find and Delete Duplicate Record in SQL

--Retrieve duplicate record
SELECT * FROM MovieDB
    WHERE Title in (SELECT Title FROM MovieDB GROUP BY Title HAVING COUNT(Title) > 1)


--Delete duplicate record,keep record with low Id value

DELETE FROM MovieDB
    WHERE Title IN (SELECT Title FROM MovieDB GROUP BY Title HAVING COUNT(Title) > 1) AND
    Id NOT IN (SELECT min(Id) FROM MovieDB GROUP BY Title HAVING COUNT(Title)>1)


--Replace value

UPDATE TableName SET columnName = REPLACE(columnName, 'previous value', 'new value')

--Updating value based on other table

UPDATE TableOne
SET Date1 = TableTwo.Date2
FROM TableOneTableTwo
WHERE
   TableOne.Title = TableOne.Title AND Date1 IS NULL


--Insert file value to table

BULK INSERT URLDate
FROM 'C:\Users\Admin\Desktop\sourceData.txt'
WITH    (
FIRSTROW = 1,   --data begin
FIELDTERMINATOR = '###',  --field delimiter
ROWTERMINATOR = '\n',   --Use to shift the control to next row
TABLOCK)


No comments:

Post a Comment

Setup VNC on Ubuntu 20.04

  sudo apt update sudo apt install xfce4 xfce4-goodies sudo apt install tigervnc-standalone-server sudo apt install tightvncserver vncserver...