Loading ...
Sorry, an error occurred while loading the content.

4494Re: [SQLQueriesNoCode] RE: UPDATE question

Expand Messages
  • Tim Mitchell
    Apr 5 12:14 PM

      You can't do an UPDATE... SELECT, but you can do an UPDATE with an INNER
      JOIN to get the desired result. Something like the following should work:

      UPDATE m
      SET m.field1 = t1.field1
      , m.field2 = t2.field2
      , m.field3 = t3.field3
      FROM Master m
      INNER JOIN Table1 t1 ON <join conditions>
      INNER JOIN Table2 t2 ON <join conditions>
      INNER JOIN Table3 t3 ON <join conditions>

      Let me know if this doesn't answer your question....
      Tim Mitchell, MCTS
      SQL Server MVP
      Blog: http://www.TimMitchell.net
      Twitter: http://twitter.com/Tim_Mitchell

      On Mon, Apr 5, 2010 at 2:04 PM, John Warner <john@...> wrote:

      > SQL Server 2005 Express
      > I need to UPDATE 3 fields in a table let's call it Master.
      > The data is gotten from a SELECT which fetches all 3 of the needed fields
      > and quite a few rows.
      > Can I do something like
      > UPDATE Master
      > SET field1, field2, field3
      > (SELECT field1, field2, field3
      > FROM tables, join ....
      > WHERE prime key table1 = table2 etc) SEL
      > WHERE primeKey Master = PrimeKey SEL
      > Am I being clear here because the syntax is obviously not right but I hope
      > shows what I'm after.
      > The rows in Master will exist and the key will exist, I just need to put
      > data in three fields in Master gathered from a nested query.
      > Also the nested query has nested queries, each field is pulled from a
      > different table and the data is pivoted so the above nest does not really
      > reflect what is happening. If desired I can post just trying to save a bit
      > of space.
      > Thanks for the lesson I hope I'm about to receive!
      > John Warner

      [Non-text portions of this message have been removed]
    • Show all 18 messages in this topic