He extraído los siguientes datos y me gustaría reemplazar x, y, z de (2b24d001) con x, y, z de (2b25d001) basado en la misma columna igual de hole_no (por ejemplo, si hole_no para 2b24d001 = 2b25d001).

hole_id     block_id    xcollar     ycollar  zcollar  HOLE_NO
2B24D001B01 2B24D001    4382.693    3858.52  109.725    B01
2B25D001B01 2B25D001    5372.693    3358.52  392.5      B01
2B25D001B02 2B25D001    7383.893    9356.03  662.5      B02
2B24D001B02 2B24D001    5373.893    3656.03  969.525    B02
2B24D001B03 2B24D001    8876.038    3352.216 399.38     B03
2B25D001B03 2B25D001    5376.038    3752.216 392.5      B03
1B13D078B03 1B13D078    1628.253    3956.26  692.53     B03
1B12D078B03 1B13D078    1828.253    3556.26  392.53     B03
-2
Uno 10 sep. 2018 a las 12:48

3 respuestas

La mejor respuesta

Creo que quieres una update / join, pero así:

UPDATE t1
    SET xcollar = t2.xcollar,
        ycollar = t2.ycollar,
        zcollar = t2.zcollar
    FROM T t1 JOIN
         T t2
         ON t1.hole_no = t2.hole_no
    WHERE t1.block_id = '2b24d001' AND
          t2.block_id = '2b25d001';
0
Gordon Linoff 10 sep. 2018 a las 10:41

Puedes usar row_number ()

select * from
 (
 select * from
(
select *,row_number() over(partition by HOLE_NO order by case when block_id='2b24d001' then 0 case when block_id='2b25d001' then 1 end desc ) as rn 
form tablet
) as t
where rn=1
) t1 innerjoin tablet t2 on t1.block_id=t2.block_id

O puedes usar cte en lo mismo

with cte as
(

select * from
    (
    select *,row_number() over(partition by HOLE_NO order by case when block_id='2b24d001' then 0 case when block_id='2b25d001' then 1 end desc ) as rn 
    form tablet
    ) as t
    where rn=1
) select * from cte inner join tablet t2 on cte.block_id=t2.block_id
1
Zaynul Abadin Tuhin 10 sep. 2018 a las 10:05

Si entiendo correctamente, parece que quieres hacer UPDATE JOIN, JOIN en base a REPLACE (hole_id,block_id,'').

UPDATE t1
SET xcollar = t2.xcollar,
    ycollar = t2.ycollar,
    zcollar = t2.zcollar
FROM T t1 JOIN T t2
    ON REPLACE (t1.hole_id,t1.block_id,'') = REPLACE (t2.hole_id,t2.block_id,'')

sqlfiddle

0
D-Shih 10 sep. 2018 a las 10:04