UPDATING a table based on a FROM clause, how do I limit with WHERE?

Question

I have a SQL UPDATE clause which looks like:

UPDATE table 
SET column =value
FROM
(SELECT bla bla FROM bla bla WHERE col = val)
JOIN
(SELECT bla bla FROM bla bla WHERE col = val)

I want to limit the UPDATE to WHERE a particular column is equal to a particular value.

It doesnt appear to be legal to insert the WHERE after the JOIN or after the SET? I thought I had already limited the update using the JOIN but it doesn't appear so.

Where can I insert my WHERE clause?

Answer 1

Your example has been stripped down so far that it's hard to tell exactly what you are after.

It might just be a matter of giving your nested selects aliases like so:

Update table 
  Set column =value
From
  (Select bla bla From bla bla Where col = val) a
    Join
  (Select bla bla From bla bla Where col = val) b
    On a.blah = b.blah -- did this go missing from the example?
Where
  ...

SQL Server has an update form that may help:

Update
  t1
Set
  blah
From
  table1 t1 -- note same as updated table
    inner join
  (select...) a
    On t1.blah = a.blah
Where
  t1.Col = value

Answer 2

If this vlaue is a literal value, not a value coming form either of these joined tables, then you can do this:

UPDATE table t1
SET t1.column = value -- literalvalue
FROM
(
   SELECT bla bla FROM bla bla)
   JOIN
   (
      SELECT bla bla FROM bla bla
   ) on thefirstblah = somethingfromthesecondblah
) t1

Or:

UPDATE table t1
SET t1.column = value -- literalvalue
FROM
(
   SELECT bla bla FROM bla bla
   JOIN anotherbla ON ----
) t1;

However, if this value is coming from one of the joined table:

UPDATE table t1
SET t1.column = t2.value 
FROM table t1
JOIN table2 t2 ON ---