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

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...

From stackoverflow

• 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.

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

• 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.

• Try folowing

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