插入到select中并在单个查询中更新 [英] Insert into select and update in single query

问题描述

I have 4 tables: tempTBL, linksTBL and categoryTBL, extra

在我的tempTBL上，我有:ID，名称，URL，猫，被插入的列 在我的linksTBL上，我具有:ID，名称，别名列 在我的类别TBL上，我有:cl_id，link_id，cat_id 在我的extraTBL上，我有:id，link_id，值

on my tempTBL I have: ID, name, url, cat, isinserted columns on my linksTBL I have: ID, name, alias columns on my categoryTBL I have: cl_id, link_id,cat_id on my extraTBL I have: id, link_id, value

如何执行单个查询以从tempTBL中选择isinsrted = 0的所有项目，然后将它们插入linksTBL，并为插入的每个记录选择拾取ID(主要的)，然后将该ID插入cat_id = 88的categoryTBL中.之后，为link_id插入extraTBL ID，并为值插入url.

How do I do a single query to select from tempTBL all items where isinsrted = 0 then insert them to linksTBL and for each record inserted, pickup ID (which is primary) and then insert that ID to categoryTBL with cat_id = 88. after that insert extraTBL ID for link_id and url for value.

我知道这是如此令人困惑，无论如何我都会张贴这个...

I know this is so confusing, put I'll post this anyhow...

这是我到目前为止所拥有的:

This is what I have so far:

INSERT IGNORE INTO linksTBL (link_id,link_name,alias)
VALUES(NULL,'tex2','hello');         # generate ID by inserting NULL

插入INTO类别TBL(link_id，cat_id) VALUES(LAST_INSERT_ID()，'88'); #在第二张表中使用ID

INSERT INTO categoryTBL (link_id,cat_id) VALUES(LAST_INSERT_ID(),'88'); # use ID in second table

我想在这里添加一个地方，它仅选择isinserted = 0并插入那些记录的项目，并且插入的onse会更改为isinserted变为1，因此下次运行时将不再添加它们.

I would like to add here somewhere that it only selects items where isinserted = 0 and iserts those records, and onse inserted, will change isinserted to 1, so when next time it runs, it will not add them again.

推荐答案

这不可能在单个查询中完成.您将必须插入行，然后运行单独的更新语句.

this is not possible to do in a single query. you will have to insert the rows, then run a separate update statement.

这篇关于插入到select中并在单个查询中更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！