sql server delete duplicate records from a table after checking if these records are there in dependent table -


i have 2 tables below

table_1
app_med_chip_id   application_id    person_id
248340                     1228144                   1028940
248342                     1228144                   1028940

328526                   1273218                   818905
328527                   1273218                   1386405
328528                   1273218                   1386407

table_2
app_med_chip_detail_id    app_med_chip_id
92574                                        248342

in table 1 first 2 records duplicates have same application_id , person_id, need take app_med_chip_id (248340,248342) of these 2 records, check if these present in table 2, delete record table 1 not present in table 2.

i have used below query select duplicates.

select * <br> (select row_number() on (partition application_id,person_id order application_id,person_id) row,*  table_1) p<br> p.app_med_chip_id not in<br>  (select app_med_chip_id table_2)<br> , p.row > 1

problem query first set of records in table_1, result set not show first set of records used condition p.row > 1, if not use condition not able filter duplicates.

i have shown 2 sets of records example, there many other records in table_1 this.

please advise on how solve this.

thank you.

delete record table 1 not present in table 2

so, think want delete "duplicate" rows in table_1 not exist in table_2. want delete rows when there more record when grouped application_id , person_id.

you this:

delete table_1 app_med_chip_id not in (     select app_med_chip_id table_2 ) , exists (     select 1 table_1 t1     table_1.application_id = t1.application_id        , table_1.person_id = t1.person_id     having count(*) > 1 )

of course, root issue dirty data allowed in table_1. if have control on ddl, put constraint on table_1, such fk table_1.app_med_chip_id references table_2.app_med_chip_id or unique constraint on table_1 columns (application_id, person_id). if not have control on ddl, feel you.


Comments

Post a Comment

Popular posts from this blog

javascript - RequestAnimationFrame not working when exiting fullscreen switching space on Safari -

request animation frame stops working when exiting fullscreen clicking on original space's safari window. fine if fullscreen mode canceled escape key or calling cancelfullscreen(). steps reproduce: open https://dl.dropboxusercontent.com/u/769042/prezi/safari-fullscreen.html click "draw raf", kittie appears click "fullscreen", go fullscreen click "draw raf", kittie appears go space original safari was, showing "click exit fullscreen mode", click anywhere, out fullscreen click "draw raf", nothing happens what handling click calling: window.requestanimationframe(draw); which draws on canvas context: function draw() { ctx.drawimage(img, math.random()*500|0, math.random()*400|0, 100, 100); } i checked .hidden , .visibilitystate, updated correctly. tested on osx 10.9.3, safari 7.0.4 (9537.76.4). has workaround/solution other switching old settimeout? this sounds webkit bug 88940 : using reque...
Read more

jsf - How to ajax update an item in the footer of a PrimeFaces dataTable? -

this visual of table have: +---------+------------+-------------------+ | header1 | header2 | header3 | +---------+------------+-------------------+ | row | input | calc'ed output | | row b | input b | calc'ed output b | | etc.. | etc.. | etc.. | +---------+------------+-------------------+ | total: total calc'ed output | +------------------------------------------+ and stripped-down code: <p:datatable id="mytable" value="#{mybean.somelist}" var="currentitem"> <p:column headertext="header1"> <h:outputtext value="#{currentitem.name}" /> </p:column> <p:column headertext="header2"> <pe:inputnumber value="#{currentitem.inputval}"> <p:ajax event="change" listener="#{mybean.changelistener}" update="outputval outputtotal...
Read more

django - CSRF verification failed. Request aborted. CSRF cookie not set -

error: reason given failure: csrf cookie not set. in general, can occur when there genuine cross site request forgery, or when django's csrf mechanism has not been used correctly. post forms, need ensure: browser accepting cookies. view function uses requestcontext template, instead of context. in template, there {% csrf_token %} template tag inside each post form targets internal url. if not using csrfviewmiddleware, must use csrf_protect on views use csrf_token template tag, accept post data. you're seeing section of page because have debug = true in django settings file. change false, , initial error message displayed. can customize page using csrf_failure_view setting. did find, nothing helped, must foolish(( my template <form method="post">{% csrf_token %} {% field in form %} <div class="control-group"> {{ field }} </div> {% endfor %} <input type=...
Read more