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

    Unanswered: Updating multiple rows with multiple criteria

    Hi experts.

    I didn't get the chance to browse if this question is already been asked and answered already so please forgive me if I double posted.

    I just wanted to ask if is there a way to update multiple rows in one update query in tsql? what I wanted to do is for example I got a table containing

    code : desc
    1 : a
    2 : b
    3 : c
    4 : d
    1 : e
    3 : f

    I wanted to update it to
    code : desc
    1 : x
    2 : b
    3 : y
    4 : d
    1 : x
    3 : y

    I hope my question make sense, any one got an idea how to do it?

    thanks

  2. #2
    Join Date
    Oct 2009
    Posts
    3
    Because what I am doing at the moment is

    use mydbase
    go
    update mytable set desc = "x" where code in ('1','10','abc','gdx');
    go
    update mytable set desc = "y" where code in ('3','33','3433','3etc');

    is there a simpler way?

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Is this what you are after?
    Code:
    UPDATE MyTable 
       SET desc = CASE WHEN code IN('1', '10', 'abc', 'gdx')
                         THEN 'x'
                       WHEN code IN('3', '33', '3433', '3etc')
                         THEN 'y'
                       ELSE desc 
                  END
    WHERE code IN ('1', '10', 'abc', 'gdx',
                   '3', '33', '3433', '3etc')
    If you omit the WHERE clause it will update each and every record in MyTable, sometimes updating it with the already present value. If you keep an audit trail, you may want to avoid that.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Oct 2009
    Posts
    1
    Hi all,

    Apologises in advance for the long windedness of this post and also what ever mistakes I may make. This is my first time writing on a forum, I usually just read them and take snippets of info from here and there.

    Anyway to my point! I am Data Analyst that is self taught in MS Access (2003). I have been able to utilise the knowledge I have to create applications that automate a variety of tasks that were previously manually carried out in arduous .xls worksheets. Well the tools(applications) I have created are now being used multiple user and I have created Front End/Back End systems, I have also taught my self some basic (anthesis on basic) VBA mainly Docmd features and such.

  5. #5
    Join Date
    Oct 2009
    Posts
    3
    Quote Originally Posted by Wim
    Is this what you are after?
    Code:
    UPDATE MyTable 
       SET desc = CASE WHEN code IN('1', '10', 'abc', 'gdx')
                         THEN 'x'
                       WHEN code IN('3', '33', '3433', '3etc')
                         THEN 'y'
                       ELSE desc 
                  END
    WHERE code IN ('1', '10', 'abc', 'gdx',
                   '3', '33', '3433', '3etc')
    If you omit the WHERE clause it will update each and every record in MyTable, sometimes updating it with the already present value. If you keep an audit trail, you may want to avoid that.
    Works like a charm! thank you very much Wim!

  6. #6
    Join Date
    Jan 2010
    Posts
    1
    Quote Originally Posted by Wim View Post
    Is this what you are after?
    Code:
    UPDATE MyTable 
       SET desc = CASE WHEN code IN('1', '10', 'abc', 'gdx')
                         THEN 'x'
                       WHEN code IN('3', '33', '3433', '3etc')
                         THEN 'y'
                       ELSE desc 
                  END
    WHERE code IN ('1', '10', 'abc', 'gdx',
                   '3', '33', '3433', '3etc')
    If you omit the WHERE clause it will update each and every record in MyTable, sometimes updating it with the already present value. If you keep an audit trail, you may want to avoid that.
    It is Wonderful. Thousands Thanks

Posting Permissions

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