在一个 SQL 查询中结合 INSERT 和 SELECT (Zapier) [英] Combine INSERT and SELECT in one SQL query (Zapier)
问题描述
如果没有指定唯一键的条目或什么都不做，我正在制作 Zapier 任务以在 MySQL 中插入条目.
I'm crafting Zapier task to insert entry in MySQL if there is no entry with specified unique key or do nothing.
我需要尝试在某个表中插入新行，但如果新条目的电子邮件已经是 INSERT 将静默发出警告(由于 IGNORE 关键字).
I need to try to insert new row into some table, but if email of new entry is already INSERT will silently throw warning (due to IGNORE keyword).
INSERT IGNORE users(email, hashed_password)
VALUES ('<email>`, '<some_hashed_password>')
但在这两种情况下，我的查询都没有返回任何内容，Zapier 会用此消息结束任务:
But in both scenarios my query is not returning anything and Zapier ends task with this message:
问题:有什么方法可以让一个复杂的 SQL 命令结合 INSERT 和 SELECT，所以通过一个查询，我将从中获得一些结果集DB，不是空对象还是 INSERT 返回的任何内容?
Question: Is there some way to have one complex SQL command that will combine INSERT and SELECT so with one query I will get some result set from from DB, not empty object or whatever INSERT returns?
P.S. 这适用于 MySQL:
P.S. This works in MySQL:
INSERT IGNORE reporting.users(`email`, `password`)
VALUES ("test@test.ts", "test");
SELECT * FROM reporting.users as u WHERE u.email = "test@test.ts";
但这包含两个查询，这在 Zapier 中不起作用.
but this consists of two queries and this doesn't work in Zapier.
推荐答案
这是一个老问题，但我今天也在努力解决同样的问题.在试图找到一个解决方案时，我遇到了这个 qn，所以当我找到一个解决方案/解决方法时，我想我会做一些体面的事情并回帖......
This is an old question but I was grappling with the same issue today. In trying to find a solution I came across this qn and so when I found a solution / work-around I thought I'd do the decent thing and post back...
基于他们帖子中的红色Bargle"错误，我相信 zmii 一定一直在尝试使用 MySQL 自定义搜索查询.Zapier 必须有查询的输出，否则会出错.我环顾四周并因此制作了我的自定义查询:
Based on the red "Bargle" error in their post I believe zmii must have been trying to use the MySQL custom search query. Zapier has to have an output from the query or it faults. I did some looking around and crafted my custom query thus:
SELECT IFNULL( (SELECT employee_id FROM timesheets.employees WHERE employee_id = <Step 6 | sheet_data_id> LIMIT 1) ,0) AS result;
基于所选答案此处.
因此，如果找到，我的查询将输出员工 ID，否则将输出 0.然后，我根据自定义查询的输出结果"插入了 Zapier PATHS 步骤.如果结果大于 0，则更新现有记录.如果它是 0 那么我插入一个新记录.我怀疑我可以使用自定义查询代码来进行分支和更新/插入，但我还没有尝试，因为我还有其他事情要先尝试.
So, my query will output an employee ID if it is found and 0 if not. I then inserted a Zapier PATHS step which I based on the output 'result' from my custom query. If the result is greater than 0 I update an existing record. If it is 0 then I insert a new record. I suspect I could use the custom query code to do the branching and updating/inserting but I didn't try that yet as I have other things to try first.
编辑实际上，我不得不根据与 Zapier 此处.我保留了原始答案但隐藏了它.语法有效，但仅当查询是 SELECT 查询时，它不适用于 INSERT 或 UPDATE 查询.有关详细信息，请参阅链接问题的评论中的讨论.在此阶段，基本上无法通过 Zapier MySQL 自定义查询步骤执行插入或更新操作.
Edit
Actually I have had to revise this answer based on my conversation with Zapier here. I've retained the original answer but hidden it. The syntax works but only if the query is a SELECT query, it will not work for an INSERT or UPDATE query. See the discussion in the comments of the linked question for details. Essentially it is not possible to do an insert or update operation via the Zapier MySQL Custom Query step at this stage.
这篇关于在一个 SQL 查询中结合 INSERT 和 SELECT (Zapier)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
