sql - Matching each row of one table with one row of another table -

i 've got table:

table_a -----------   sc cl pr br ----------- 1  1  1  null 2  1  1  null 1  2  2  null 2  2  2  null  

(sc, cl, pr) candidate key

in order assign values field br (take granted cannot change) insert these rows table_b 3 fields (br, cl, pr) field br auto incremented (to make things simpler assume table_b empty , table_a contains above rows). that

insert table_b (cl, pr) select cl, pr table_a 

now want assign auto generated table_b.br table_a.br in such way that

table_b.cl = table_a.cl , table_b.pr = table_a.pr

and every 2 rows of table_a t1, t2

t1.cl = t2.cl , t1.pr = t2.pr  , t1.sc <> t2.sc => t1.br <> t2.br 

ps 1: hope not confusing :(

ps 2: quick , easy solution 've find add field in table_b (sc) simple join between tables job. adding column not option.

this seems more puzzle problem. can't drop column br , add again auto incrementing one?

anyway, here's solution using row_number() partition :

update set a.br = b.br     ( select cl            , pr            , row_number()                 over(partition cl, pr order sc)              rowno            , br       table_a     )   join     ( select cl            , pr            , row_number()                over(partition cl, pr order br)              rowno            , br       table_b     ) b     on  a.cl = b.cl     , a.pr = b.pr     , a.rowno = b.rowno; 

on second thought, don't need table_b. can assign auto values field br with:

update set a.br = a.rowno     ( select row_number()                 over(order cl, pr, sc)              rowno            , br       table_a     ) a;