update set from 以及表 数据的复制 insert into select from ....

insert into ... select  from ...

工作中的示例： 初始化数据

INSERT INTO "db_gyfw"."t_fa_capz"

( "c_bh", "c_bh_ryfz", "n_bh_fy", "n_hlca", "n_fpcs", "dt_cjsj", "dt_zhgxsj", "n_zsbl" ) 
SELECT
uuid (),
fz.c_bh,
fz.n_bh_fy,
'2',
'10',
now(),
now(),
'0.1' 
FROM
    db_gyfw.t_fa_ryfz fz;

转载其他博主：https://blog.youkuaiyun.com/qq_21612319/article/details/79780822

select into from 和 insert into select都是用来复制表，两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建。insert into select from 要求目标表存在。

    备份表数据： create table emp as select * from scott.emp

    还原表数据：insert into emp select * from scott.emp

    复制表结构及其数据：

    create table table_name_new as select * from table_name_old

    只复制表结构：

    create table table_name_new as select * from table_name_old where 1=2;

    或者：

    create table table_name_new like table_name_old

    只复制表数据：

    如果两个表结构一样：

    insert into table_name_new select * from table_name_old

    如果两个表结构不一样：

    insert into table_name_new(column1,column2...) select column1,column2...

    from table_name_old pasting

 update set from ...

其他博主链接： https://blog.youkuaiyun.com/zeternityyt/article/details/80041794

                          https://www.cnblogs.com/liuqichun/p/4500487.html

 

MySQL 和 SQLSERVER不一样，update set from 一张表的时候 应该改为

UPDATE TABLE_AA INNER JOIN TABLE_BB ON TABLE_AA.ID = TABLE_BB.ID SET NAME = TABLE_BB.NAME

mysql 和postgresql中这样使用

update t_fa_rycs
inner join 
     (select c_bh,n_bh_ry from t_fa_fzry group by n_bh_ry having count(*) =1) s on s.n_bh_ry = t_fa_rycs.n_bh_ry set t_fa_rycs.c_bh = s.c_bh 

示例：

UPDATE BJ_CaiLiao
SET
    Mat_GuiGe = gg.Mat_GuiGe_Title
FROM 
(
SELECT bcl.Mat_Id,bcl.Mat_GuiGe
,ma.Mat_GuiGe_ID
,mgg.Mat_GuiGe_ID AS Mat_GuiGe_ID1,mgg.Mat_GuiGe_Title 
FROM BJ_CaiLiao bcl
LEFT JOIN Mat_Attach ma ON ma.Mat_Id = bcl.Mat_Id
LEFT JOIN Mat_GuiGe mgg ON mgg.Mat_GuiGe_ID = ma.Mat_GuiGe_ID
) gg

WHERE BJ_CaiLiao.Mat_Id = gg.Mat_Id；

第二种方式

UPDATE "db_gyfw".t_fa_rycs cs,
(
SELECT COUNT
    ( ry.n_bh_ry ),
    ry.n_bh_ry,
    cs.c_bh csbh,
    ry.c_bh rybh 
FROM
    "db_gyfw".t_fa_fzry ry
    LEFT JOIN t_fa_rycs cs ON ry.n_bh_ry = cs.n_bh_ry 
GROUP BY
    ry.n_bh_ry 
HAVING
    COUNT ( ry.n_bh_ry ) = 1 
    ) s 
    SET cs.c_bh = s.rybh 
WHERE
    cs.n_bh_ry = s.n_bh_ry;