Updating multiple rows for multiple values

  • Hi,

    I wasn't sure how to phrase my query so wasn't sure what to search for.

    I'm attempting the simplest of updates - e.g. "UPDATE CRnumbers SET StudyLocationID = " + CLocation.Text + " WHERE RegNumber = "+UserEntry.text

    UserEntry might be '12345', but how do I update for multiple entries?

    So if UserEntry has '12345,12347,12399' my super-newbie method would be to separate the comma separated values and loop through each one updating each individually, but this is obviously not the way to go.

    How do I structure my SQL to update all 3 of those values in one hit? Do I create a table from my CSVs and use that somehow or is there a simple command that understands multiple values like that?

    Thanks in advance, folks.

  • You didn't say what's in CLocation.Text. Is that also a delimited string with the same number of values? Or perhaps just a single value? Or some combination?


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Sorry, yes (and thanks for replying).

    CLocation.text is just a single value.

  • simonwestb (9/2/2012)


    Hi,

    I wasn't sure how to phrase my query so wasn't sure what to search for.

    I'm attempting the simplest of updates - e.g. "UPDATE CRnumbers SET StudyLocationID = " + CLocation.Text + " WHERE RegNumber = "+UserEntry.text

    UserEntry might be '12345', but how do I update for multiple entries?

    So if UserEntry has '12345,12347,12399' my super-newbie method would be to separate the comma separated values and loop through each one updating each individually, but this is obviously not the way to go.

    How do I structure my SQL to update all 3 of those values in one hit? Do I create a table from my CSVs and use that somehow or is there a simple command that understands multiple values like that?

    Thanks in advance, folks.

    There is a simple "command"

    "UPDATE CRnumbers SET StudyLocationID = " + CLocation.Text + " WHERE RegNumber IN ( "+UserEntry.text + ")"

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Excellent! That's exactly what I needed. Thank you.

  • simonwestb (9/3/2012)


    Excellent! That's exactly what I needed. Thank you.

    Now all you have to do is to make sure no one can pull any SQL Injection on you. And, yeah... there are [font="Arial Black"]LOTS [/font]of articles on that subject because so many people have been nailed by it on things just like this.

    I can help avoid SQL Injection on the comma delimited string of numbers but what goes into "CLocation.Text"? And, yeah... I'm strongly recommending that you DON'T use that code until you make it bullet proof. It's just waiting to be attacked.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the tip.

    Security is something I was planning to tackle at a later stage when I'd got the bulk of my functionality working. I realise that'll mean changing a lot of my code, but I'm trying to take things one step at a time. Most of queries are in the VB end too, which I know I'll need to transform into stored procedures sooner or later.

    Fun times ahead I think!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply