Replace original srids with new ones

As you can see, I have got 2 tables here, table 1 and table 2. The column srid in Table 1 is an identity column.

Table 2开发者_如何转开发 is like a child table of table 1, where we can link table 2 with table 1 by using srid column.

Now what I would like to achieve is to sort of replace the original srid, i.e. 1,2,3 with the new ones, i.e. 4,5,6, while in the mean time we keep its original crvalue unchanged. And then we will insert it back into table 2.

I find replacing new srids is a bit challenging and get stuck. So what query/script should I write to achieve this?

Thanks.

You could try something like this:

WITH ranked AS (
  SELECT
    *,
    rank = ROW_NUMBER() OVER (PARTITION BY uid ORDER BY srid)
  FROM Table1
)
UPDATE t2
SET srid = new.srid
FROM Table2 t2
  INNER JOIN ranked old ON old.uid = 1 AND old.srid = t2.srid
  INNER JOIN ranked new ON new.uid = 2 AND new.rank = old.rank

Basically, you are ranking srid values within each uid group and then joining the two subsets by the ranks to associate every old srid with a new one. Naturally, you are joining Table2 with the old srid subset too, of course.

based on this simple example i think you could inner join the tables like this

...FROM Table1 INNER JOIN Table2 ON Table1.srid = Table2.srid + 3...

you will then insert Table1.srid and Table2.crvalue into the new table. These results should return your last table. I hope this helps.