我可以在SQL UPDATE内使用内部SELECT吗? [英] Can I have an inner SELECT inside of an SQL UPDATE?

问题描述

我有一个数据库，如:

表foo具有列id和name 表bar具有列id和foo_id

Table foo has columns id and name Table bar has columns id and foo_id

我有一个带有foo.name的传入HTTP查询，我想在bar中插入一行，并适当设置bar.foo_id.因此，例如:

I have an incoming HTTP query with a foo.name, I'd like to insert a row into bar with bar.foo_id set appropriately. So, for example:

> SELECT * FROM foo;
id     name
------ -------
1      "Andrey"
(1 row)


> SELECT * FROM bar;
(0 rows)

给出"Andrey"，我可以执行以下单个查询来获取:

Given "Andrey", is there a single query I can execute to get:

> SELECT * FROM bar;
id     foo_id
------ -------
1      1
(1 row)

我在考虑以下方面:

> UPDATE bar SET foo_id=(SELECT id FROM foo WHERE foo.name=?)

但这似乎是错误的，因为SELECT的返回集是值而不是值...

But this seems to be wrong, as SELECT's return sets, not values...

推荐答案

您将必须

SELECT TOP 1 ID FROM foo where foo.name=?

但是除此之外，在更新中进行选择没有任何问题.

but other than that there is nothing wrong with doing a select in an update.

这篇关于我可以在SQL UPDATE内使用内部SELECT吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！