单个表的联表更新

由于系统对接，友商给的数据比较乱，导致很多数据不是最新数据，所以我负责模块操作会出很多问题，所以我想改变顺序。在我用的表里面是表示顺序的字段如下图：

现在问题是contract_seq为1的不是最新的合同，我要找出最新的合同，并且要把不是最新的合同的contract_seq的值改成最新合同的contract_seq的值，而且我还要把最新合同的contract_seq的值改成1，单条的话处理起来很简单，写个脚本就行了，脚本如下：

update BASIC_CONTRACT_LABOR
set CONTRACT_SEQ = (select CONTRACT_SEQ from BASIC_CONTRACT_LABOR where WHETHER_EFFECTIVE = 'Y'
and USER_CODE = '01900435')
where CONTRACT_SEQ ='1'
and USER_CODE = '01900435';

update BASIC_CONTRACT_LABOR
set CONTRACT_SEQ = '1'
where WHETHER_EFFECTIVE = 'Y'
and USER_CODE = '01900435';
commit;

但是如果多条的话，就需要联表更新了，不过关联的是要更新的表。sql如下（好吧，这条sql不是我写出来的，是问了同事的）：

update BASIC_CONTRACT_LABOR a
set a.CONTRACT_SEQ = 
(select CONTRACT_SEQ from BASIC_CONTRACT_LABOR c where WHETHER_EFFECTIVE = 'Y'
and USER_CODE in ('01900436','03138511')  and a.USER_CODE = c.USER_CODE)
where a.CONTRACT_SEQ ='1'
and a.USER_CODE  in ('01900436','03138511')


update BASIC_CONTRACT_LABOR
set CONTRACT_SEQ = '1'
where WHETHER_EFFECTIVE = 'Y'
and USER_CODE = '01900435';
commit;

今天又用到个升级版，emmmm，贴上来，以后用起来方便。sql语句如下：

UPDATE  BASIC_CONTRACT A
    SET A.CONTRACT_SEQ = 
    ( SELECT CONTRACT_SEQ FROM BASIC_CONTRACT B 
        WHERE USER_CODE IN ('03035064','03829791') AND A.USER_CODE = B.USER_CODE 
        AND CONTRACT_CODE = (SELECT MAX(CONTRACT_CODE) FROM BASIC_CONTRACT C WHERE USER_CODE IN ('03035064','03829791') AND B.USER_CODE = C.USER_CODE ))
WHERE A.CONTRACT_SEQ = '1'
AND A.USER_CODE IN ('03035064','03829791');

UPDATE BASIC_CONTRACT A
SET  A.CONTRACT_SEQ = '1'
WHERE A.USER_CODE IN ('03035064','03829791')
AND  A.CONTRACT_CODE = (SELECT MAX(CONTRACT_CODE) FROM BASIC_CONTRACT B WHERE USER_CODE IN ('03035064','03829791') AND B.USER_CODE = A.USER_CODE );
COMMIT;