CODEWITHSUNDEEP
sqlsql-server
Christian R
Christian R

Reputation: 313

Update Value in SQL Table and Delete Redundant Entries

I have this database with the following tables that have many redundant records from various insert into table statements rather than updating the already existing data. Is there a way to delete the redundant records without losing the data in the table?

My code: 

SELECT EMP_NUM,
       EMP_LNAME,
       EMP_FNAME,
       EMP_INITIAL,
       EMP_HIREDATE,
       JOB_CODE,
       EMP_PCT,
       PROJ_NUM
FROM EMP_2

My second question is I need to write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103.

Upvotes: 0

Views: 75

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37377

First of all, you need to determine which records are reduntant and which ones should stay, there are two ways:

  1. You don't care, so you can just do select distinct into some temporary table to get unique rows (so without redundancy), then delete the table and insert back unique vlues.

  2. You have some column, upon which you decide which rows, from group of rows will be the one to preserve. For example you have dateOfInsert column, so you could use such query:

    select *, row_number() over (order by dateOfInsert desc) rn from MyTable

then, insert recrods to temporary table with rn = 1, delete the table, and insert only the newest records.

Regarding second question, it is basic:

update MyTable set EMP_PCT = 3.85 
where EMP_NUM = 103

Upvotes: 1

Related Questions