3

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?

intrigued_66
  • 16,082
  • 51
  • 118
  • 189

3 Answers3

2

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
Laurence
  • 10,896
  • 1
  • 25
  • 34
1

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 ---
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
0

Use query something like this :

Important thing that worked for me was join before update after.

update table1 t1 join table2 t2 on t1.id1 = t2.id2 set t1.value = t2.otherValue;
enigma
  • 31
  • 5