Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Unanswered: TSQL:Update multiple rows based on multiple values

    Lets say I have this:

    Code:
        Declare @passRefID as int
        Declare @passTextA as varchar
        Deckare @oassTextB as varchar
        Declare @passPropertyA as int
        Declare @passPropertyB as int
    
       
        Set @oassTextA = 'Joe'
        Set @passTextB = 'Smith'
        Set @passPropertyA = 21
        Set @passPropertyB = 23
    **TSQL A:**

    Code:
    Set @passRefID = Select Ref_ID from TableA where ID = 10
    The `Ref_ID` returns a value of 50

    Now I want to use that value in another select statement which returns any amount of rows.
    It looks like this

    **TSQL B:**

    Code:
    Select UserID from TableB where FK_RefID = @passRefID
    So lets say it returns:

    UserID
    34
    56
    87

    Now I want to create an update for `TableC` based on the `UserID` that return previously.

    My **TableC** layout of records look like this:

    ID, UserID, PropertyDefinitionID, PropertyValue
    265,34,21,Bob
    266,34,23,Barker
    271,34,55,bb@abc.com
    628,56,21,Jane
    629,56,23,Adams
    635,56,55,ja@abc.com
    901,83,21,Tom
    905,83,23,Thumb
    910,83,55,tt@abc.com

    I know I can use:

    Code:
        Update TableC Set PropertyValue = @oassTextA Where UserID = 34 and PropertyDefinitionID = @passPropertyA
        Update TableC Set PropertyValue = @oassTextB Where UserID = 34 and PropertyDefinitionID = @passPropertyB
        Update TableC Set PropertyValue = @oassTextA Where UserID = 56 and PropertyDefinitionID = @passPropertyA
        Update TableC Set PropertyValue = @oassTextB Where UserID = 56 and PropertyDefinitionID = @passPropertyB
        Update TableC Set PropertyValue = @oassTextA Where UserID = 83 and PropertyDefinitionID = @passPropertyA
        Update TableC Set PropertyValue = @oassTextB Where UserID = 83 and PropertyDefinitionID = @passPropertyB
    But my problem is that in **TSQL B**, those rows returned could differ. There could be 1 row or 100 rows returned and the **TableC** is constructed a bit different from the norm.

    How can I create a dynamic `UPDATE` statement based on the amount of rows return using the unique USERIDs and the way the TableC uses the PropertyDefinitionID?

    Thanks for your assistance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use the FROM clause to include tableA and tableB in two different update statements.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Posts
    6
    Quote Originally Posted by Pat Phelan View Post
    Use the FROM clause to include tableA and tableB in two different update statements.

    -PatP
    Thanks for your reply. But I am not sure what you mean by including a FROM clause. Also I was hoping to find a different UPDATE solution instead of using the one I had since the UPDATE could be dynamic based on the USERID's returned and how TABLEC is setup.

Tags for this Thread

Posting Permissions

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