SQL update table with distinct

Question

is it possible to do an update to table with Distinct statement? I have a query here that will map the entries from another table but table1 have duplicate entries. removing the distinct statement made the output wrong.

select distinct dm.[EE],dm.document_name,lu.*
from Table1 as dm 
left join Table2 as lu on lu.[document_name]=dm.document_name
where Doc_Count=2

I made a sample scenario in sql fiddle. (http://sqlfiddle.com/#!6/634ef/1)

CREATE TABLE TABLE1
    ([EE] int, [Name] nvarchar(50),[Doc_Count] int, [document_name] nvarchar(50), [orginal_name] nvarchar(50));

INSERT INTO TABLE1
    ([EE], [Name],[Doc_Count], [document_name])
VALUES
    (001, 'Employee 1','2','Admin123.pdf'),
    (001, 'Employee 1','2', 'Admin123.pdf'),
    (002, 'Employee 2','2', 'password.pdf'),
    (002, 'Employee 2','2', 'password.pdf')


CREATE TABLE TABLE2
    ([document_name] nvarchar(50), [original_name] nvarchar(50));

INSERT INTO TABLE2
    ([document_name], [original_name])
VALUES
    ('Admin123.pdf','test444.pdf'),
    ('Admin123.pdf','test0124.pdf'),
    ('password.pdf','hello1.pdf'),
    ('password.pdf','hello2.pdf')

Results table:

EE  document_name   document_name   original_name
1   Admin123.pdf    Admin123.pdf    test0124.pdf
1   Admin123.pdf    Admin123.pdf    test444.pdf
2   password.pdf    password.pdf    hello1.pdf
2   password.pdf    password.pdf    hello2.pdf

What do you mean the output was "wrong"? What did you run? What did you expect? What did you get? — Bacon Bits, Oct 17 '17 at 21:10
To accomplise this you need to underdand 2 things, the first one is the UPDATE FROM statement (https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) and the second is PARTITION OVER (https://stackoverflow.com/questions/3317433/delete-duplicate-records-in-sql-server) — llouk, Oct 17 '17 at 21:38
Tell us, which column do you want to update and with what? And we can provide you the query — llouk, Oct 17 '17 at 21:49
Possible duplicate of [How do I UPDATE from a SELECT in SQL Server?](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) — Kittoes0124, Oct 17 '17 at 23:04
Maybe this would help: https://stackoverflow.com/questions/7163271/using-distinct-in-sql-update — Alex, Oct 18 '17 at 02:14

score 0 · Accepted Answer · answered Oct 18 '17 at 02:32

0

Is this what you are looking for

SELECT * FROM TABLE2 INNER JOIN
(
  SELECT DISTINCT  [document_name] FROM TABLE1
)T ON TABLE2.Document_Name = T.Document_Name

answered Oct 18 '17 at 02:32

Aswani Madhavan

816
6
19

what I need is for update statement but that select statement is working. – yope Oct 18 '17 at 05:48
Provide few more details :which table to be updated and column details – Aswani Madhavan Oct 18 '17 at 08:15
I need to update column "[orginal_name]" on table1 with the values coming from table2 "Original name". the mapping would be like table1.document_name= table2.document_name. – yope Oct 18 '17 at 10:08

SQL update table with distinct

1 Answers1