Query help

Transact-SQL
1

Here's the first one is mapping table and the other two is from parent table.
Mapping table--here delvid needs to be updated with updvid
Parent table--if vid exists in mapping table in column delid then that needs to be updated
but the problem is if that vid already exists with same dealid,there will be the problem as
table has to be unique(vid and dealid)--in this case we will not update but delete the minimun tid.

here,here 3972197 already exists(scrren shot 3)
also 4049233 exists and needs to be updated with 4049233.
If i update there will be one more row with same dealid and vid,in this case we need to keep just minimum tid.

Please assist.Thanks in advance

2

please post:

  1. Create table statements
  2. Insert into statements to populate the tables
  3. YOur query so far
  4. Desired output using the data from step 2
3

CREATE TABLE #tbl(
tid int,
vid int,
dealid int)

insert into #tbl
select 1 as tid,19598 as vid,22199 as dealid
union all
select 2 as tid,539532 as vid,22199 as dealid

select * from #tbl

create TABLE #Mapping(
pid int,
cid int,
upvid int,
dealvid int)
insert into #Mapping
select 135139 as pid,13300 as cid,13301 as updvid,19598 as delvid
union all
select 135139 as pid,13300 as cid,13301 as updvid,539532 as delvid

select * from #tbl
select * from #Mapping

so when updating,vid 19598 should be updated with 13301
and also 539532 should be updated to 13301,but if i do there will be two records with same vid and dealid which I don't want--
I will only keep
tid vid dealid
1 13301 22199

if it would have been different dealid but same vid, I should have both the records.

4

I did it this way:

UPDATE t
SET t.vid = m.upvid
FROM #tbl t
INNER JOIN #Mapping m
      ON t.vid = m.dealvid

DELETE t
FROM (
      SELECT t.*
            , ROW_NUMBER() OVER (
                  PARTITION BY vid
                  , dealid ORDER BY tid
                  ) AS rn
      FROM #tbl t
      ) t
WHERE t.rn > 1

SELECT *
FROM #tbl
5

sorry I didn't list the unique constraint on my scripts before.This table has a unique constraint on that vid and dealid. So is there any way to delete first and then update, else it will give me violation error on unique constrain while updating.Thanks Gbritton

6

a couple of approaches

  1. . copy the target table to a new temp table with no constraint. update that table and delete dup rows. truncate the real target table and repopulate it from the new temp table
  2. delete first and use the OUTPUT clause to write the deleted rows to a new temp table. After the delete, use the row_number trick to insert back into the table the good rows.
8

Ok.I rolled back.Used your first idea.It's working.Thanks again.