Can I have an inner SELECT inside of an SQL UPDATE?

Question

I have a database like where:

Table foo has columns id and name Table bar has columns id and 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)

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

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

I was thinking along the lines of:

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

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

Answer 1

you would have to do

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

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

Answer 2

That'll work at least on sql server and oracle.

Answer 3

This will work in MS SQL Server, if the sub query returns only one value (Such as a MAX() or TOP 1)

I'm not sure if this syntax forks in MySQL but you can try it...

UPDATE
  bar
SET
  bar.foo_id = foo.id
FROM
  bar
INNER JOIN
  foo
    ON foor.name = bar.name
ORDER BY
  foo.id

In this case, if the join returns multiple results per record in bar, all of them will be applied. The order by determining whihc order they are applied, and the last one being peristent.

Answer 4

Try folowing

UPDATE bAR SET foo_id = F.id FROM bar JOIN (SELECT id from foo where foo.name = @fooName) F