Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating multiple rows for multiple values Expand / Collapse
Author
Message
Posted Sunday, September 2, 2012 11:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 1:04 PM
Points: 4, Visits: 5
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.
Post #1353252
Posted Sunday, September 2, 2012 8:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 3,609, Visits: 5,220
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1353308
Posted Monday, September 3, 2012 6:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 1:04 PM
Points: 4, Visits: 5
Sorry, yes (and thanks for replying).
CLocation.text is just a single value.
Post #1353480
Posted Monday, September 3, 2012 7:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1353517
Posted Monday, September 3, 2012 12:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 1:04 PM
Points: 4, Visits: 5
Excellent! That's exactly what I needed. Thank you.
Post #1353617
Posted Monday, September 3, 2012 8:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
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 LOTS 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353676
Posted Tuesday, September 4, 2012 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 1:04 PM
Points: 4, Visits: 5
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!
Post #1354134
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse