常用mysql语句

– 1.数据迁移(将A表数据查询出来更新到B表)
– 将cif_customer_credentials的证件类型，身份证号码，认证状态同步至cif_customer

UPDATE
cif_customer a,
cif_customer_credentials c
SET
a.CREDENTIALS_TYPE = c.CREDENTIALS_TYPE,
a.CREDENTIALS_NUMBER = c.CREDENTIALS_NUMBER,
a.AUTH_LEVEL = c.V_STATUS
WHERE a.PARTY_ID = c.PARTY_ID ;

– 2. 数据迁移(将A表数据查询出来插入到B表)
– xlc_party_attr_credentials –> cif_customer_credentials

TRUNCATE TABLE xlstation.cif_customer_credentials;

INSERT INTO xlstation.cif_customer_credentials (
PARTY_ID,
CREDENTIALS_TYPE,
CREDENTIALS_NUMBER,
V_STATUS,
IS_DELETED,
CREATOR,
UPDATER,
CREATE_DATE,
UPDATE_DATE,
COMMENTS
)
SELECT
PARTY_ID,
CREDENTIALS_TYPE,
CREDENTIALS_NUMBER,
CASE
V_STATUS
WHEN ‘V1’ THEN ‘R’ END AS V_STATUS,
IS_DELETED,
‘system’,
‘system’,
NOW(),
NOW(),
COMMENTS
FROM
xlstation.xlc_party_attr_credentials WHERE PARTY_ID = ‘5199881’;