Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: Udate query based on another query

    How/Can I create an update query that updates a column based on another query.


    What I tried looked similar to this


    UPDATE dbo.tblNunmberOne
    SET ThisColumn = 1
    Where (Select ColumnID From tblNumber2 Where Column2 = "Hi")

    Thanks,
    Lee
    Last edited by clinel; 08-22-03 at 14:54.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to join the two tables, either explicitly using a join clause, or implicitly in your Where clause. The former is preferred:

    UPDATE dbo.tblNumberOne
    SET ThisColumn = 1
    From dbo.tblNunmberOne
    inner join tblNumber2 on tblNumber1.ColumnID = tblNumber2.ColumnID
    where tblNumber2.Column2 = 'HI'

    This is just an example, and subtle changes to the code or design of your table structure can affect the outcome, so check it first!

    blindman

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Question I got this to word

    I got the following to work. The problem with my orginal try was that I had the select returning more than one column.

    Is there a better way?


    UPDATE Table1
    SET UD2 = 1
    WHERE (Id =
    (SELECT DISTINCT
    Table1.Id
    FROM Table2 INNER JOIN
    Table1 ON Table2.PMType = Table1.PMType
    WHERE (((Table2.CompletionDate) IS NULL) AND ((Table1.CreateWo) = 1) AND ((Table1.Dept)
    = Table2.Dept) AND ((Table1.EquipType) = Table2.EquipType) AND
    ((Table1.LineNum) = Table2.LineNum) AND ((Table1.UD2) = 0))))

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think this will get you your results more directly:

    UPDATE Table1
    SET UD2 = 1
    FROM Table1
    inner join table2 ON Table2.PMType = Table1.PMType
    AND Table1.Dept = Table2.Dept
    AND Table1.EquipType = Table2.EquipType
    AND Table1.LineNum = Table2.LineNum
    WHERE Table2.CompletionDate IS NULL
    AND Table1.CreateWo = 1
    AND Table1.UD2 = 0

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •