Yes I used and UPDATE tbl_name SET field_name = CASE WHEN... and I had to use WHERE conditions match the case condition otherwise it updated everything and put a NULL value in the other records. I am working on it as we speak.
Sounds like you are trying to port inefficient VBA techniques you employed in Access to SQL Server. Learn the mantra "USE THE ENGINE".
I'll add an unimportant detail that will save you a lot of typing over time. dbo is the default schema and can be omitted.
databasename.dbo.tablename.columnnamedatabasename..tablename.columnname
You missed that the column name needs to be parameterized. (Why, I don't know - that was just the OP requirement).
As many of us have posted, that would be as simple as something like:
Yes I actually used an UPDATE SET with CASE WHEN in a stored procedure because I did need parameters and it worked. Thank you
I didn't know you could use the 3rd part of replace that way REPLACE(REPLACE(field_name, 'POS', ''), 'R', '') so thanks for the tips
Yes moving from Access VBA to SQL is tricky but I am learning. Any tips? What do you mean by "USE THE ENGINE"?
I used this with CASE WHEN for both POS and R since I didn't know that you could use it this way
OK that's interesting. Yes I am more into full SQL Server now. No Access FE. Any suggestions?Sorry, I was confused since this site is targeted at Access users. We do offer SQL support but generally it is SQL as a BE connected to an Access FE. We are always happy to help if we can but you might need to find a more targeted forum if your questions get too far afield
Queries are the go because they use the database engine's ability to process the entire data set as a whole. Code with loops is generally far slower because it processes one record at a time. Using loops and cursors is a process derided by database engineers as RBAR, Row By Agonsing Row, spoken "rebar". (Maybe whoever came up with term was also a concreter
)Yep but that was not related to the point I was making. The OP seemed to be making it complicated.
Think in terms of acting on Columns rather than Records. (Tough at first and may only make sense after you've already begun to do it)Queries are the go because they use the database engine's ability to process the entire data set as a whole. Code with loops is generally far slower because it processes one record at a time. Using loops and cursors is a process derided by database engineers as RBAR, Row By Agonsing Row, spoken "rebar". (Maybe whoever came up with term was also a concreter
TSQL does the job of both running queries and what Access does in VBA. It can do loops and cursors (the TSQL equivalent of a recordset) but the queries are usually faster unless you are processing very few records.
Some techniques to avoid RBAR, involve what are known as tally tables. It is a wide subject which you should familairise yourself with if you want to be a great developer. Here is a good example.
Say you have intermittent data but you need to process values for the days in between. The naive developer opens a recordset (cursor) and repeats the processing for the missing days. The knowledgeable developer generates a tally table of all the dates and outer joins it to the data in a query to process it all of it together.
Similar techniques of joining to a tally table are used to parse data instead of looping through it record by record.
The most important concept is that the engine processes whole sets of data at enormous speed. Avoid anything that loops and think of sets of data and you will be on the right track.
Thankfully I don't have to do it very often but I will remember that thank you. I usually only use it when executing a BCP command which is only once in awhile for a scheduled job. I will review those procedures to triple check to this, thanks.
