如何逐个执行多个查询

问题描述：

table_1 (id, first_Name, last_Name) 

table_2 (id, name, table_1_id)

我的工作是将列的所有值从table_1复制到table_2作为单独的条目。我的查询是如何逐个执行多个查询

QUERY_1：

insert into table_2 (name, table_1_id) 
    select first_Name as name, id as table_1_id from table_1.

我的其他查询是

Query_2：

insert into table_2 (name, table_1_id) 
    select last_Name as name, id as table_1_id from table_1.

它运行不错，但保存所有FIRST_NAME然后保存所有的姓氏。我的要求是这两个查询一起运行，并且希望结果会是怎样

first_Name(whatever) table_1_id (1) 

last_Name(whatever) table_1_id(1) 

first_Name(whatever) table_1_id(2) 

last_Name(whatever) table_1_id(2)

在此先感谢

注：table_1_id不table_2

答

外键可以使用达到这个一个union all：

INSERT INTO table_2(name, table1_id) 
select name, id from 
    (
    select first_name as name, id from table_1 
    union all 
    select last_name as name, id from table_1 
    ) A 
    order by id

你这是什么意思ordering_col？ – 2015-03-13 11:15:03

'ordering_col'的perpose是为了确保'first_name'总是在'last_name'前面。 – Houari 2015-03-13 11:16:25

插入的顺序无关紧要，因为表中的行没有排序顺序。因此，'orders_col'是无用的 – 2015-03-13 11:55:08

答

尝试使用WITH Queries (Common Table Expressions)

个

WITH cte AS (
    insert into table_2 (name, table_1_id) 
    select first_Name as name, id as table_1_id from table_1 
    ) 
    insert into table_2 (name, table_1_id) 
    select last_Name as name, id as table_1_id from table_1

和测试结果select * table_2 order by table_1_id

如何逐个执行多个查询

相关推荐