| Author |
Topic |
|
blinton25
Yak Posting Veteran
90 Posts |
 Posted - 2003-08-21 : 14:06:35
|
| Hello,How is everyone?I wish to update the values in a field:ID Code1 acs2 bcs3 ccsbecomesID Code1 acs2 ^bcs$3 ^ccs$Update Tablename set Code = ????? where ID > 1Can you assist? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 14:15:52
|
| This should do it:UPDATE TableNameSET Code = '^' + Code + '$'WHERE ID > 1Just had to concatenate the two characters to the Code column.Tara |
 |
|
|
GreySky
Starting Member
20 Posts |
Posted - 2003-08-21 : 14:48:27
|
| tduggan is correct.However, I'd like to point out concatenation details."^" & [Code] & "$" -- would give the same result (and is my recommended method)--The ampersand will always concatenate, even if the values are numeric (where the + would add [it's overloaded to hades]).--The ampersand does not result in null propagation, like using a plus does. For example: Null & " " returns " "; however, Null + " " returns Null. "" & Null Is Not Null. "" + Null Is Null.Gives you a neat way of saying things like:txtFullName = Trim([Prefix]+" " & [First]+" " & [Middle]+" " & [Last]) & ", "+[Suffix] & ", "+[Title]David Atkins, MCP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 14:51:59
|
| You can't use & in T-SQL. You must be talking about VB. All T-SQL code should be in stored procedures. Only executing the stored procedure should be done in VB. For numeric values, just use CONVERT.Tara |
|
|
|
GreySky
Starting Member
20 Posts |
Posted - 2003-08-21 : 14:53:31
|
| I thought this was an MS Access area. Therefore, it addresses VB and MS Access construction?And please don't be quite so dogmatic about what should *always* occur in a program. You're telling me you can't think of a reason why a temporary stored procedure should be built VB-side and then sent to SQL Server? If your imagination fails you, I'll give you an example.David |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 14:56:58
|
| Damn, didn't notice it was an MS Access question. Sorry about that. I just look at the Active Topics in SQLTeam and don't always check which forum the topic is in.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 15:19:27
|
| What do you mean by temporary stored procedure? Is that how you avoid dynamic sql?Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 15:23:29
|
| Thinking more about your last edit to your post, do you provide dbo privileges to the application account so that it can create objects or do you just grant DDL privileges to the account?Tara |
|
|
|
blinton25
Yak Posting Veteran
90 Posts |
Posted - 2003-08-21 : 18:37:45
|
| Hello,Thanks for all the information. Actually I am using Access for a Demo but will then upsize to SQL Server so the points about the differences have been noted.Side point: When I ran the query I realised that leading/trailing spaces were present, so I had to trim(Code). Once I had done that everything worked out. |
|
|
|
GreySky
Starting Member
20 Posts |
Posted - 2003-08-22 : 10:59:53
|
| Here's the example I promised... ' attempt the query update, if fail then must do item-by-item If Not ExecuteSPT("Update " & rs!ForeignObject & " Set [" & rs!ForeignColumn & "] = " & varRepID & " Where [" & rs!ForeignColumn & "] = " & lngID) Then ' prepare the temporary stored procedure that will cycle the values ' this process cannot be performed via DAO due to blocking issues strSQL = "Create Procedure #spReplaceByRowDynamic" & vbCrLf strSQL = strSQL & "AS" & vbCrLf strSQL = strSQL & "set nocount on" & vbCrLf strSQL = strSQL & vbCrLf strSQL = strSQL & "DECLARE cFKey CURSOR LOCAL FOR" & vbCrLf strSQL = strSQL & "SELECT [" & rs!ForeignColumn & "] FROM " & rs!ForeignObject & " WHERE [" & rs!ForeignColumn & "] = " & lngID & vbCrLf strSQL = strSQL & vbCrLf strSQL = strSQL & "OPEN cFKey" & vbCrLf strSQL = strSQL & vbCrLf strSQL = strSQL & "FETCH NEXT FROM cFKey" & vbCrLf strSQL = strSQL & "WHILE @@FETCH_STATUS = 0" & vbCrLf strSQL = strSQL & " BEGIN" & vbCrLf strSQL = strSQL & " UPDATE " & rs!ForeignObject & " Set [" & rs!ForeignColumn & "] = " & varRepID & " Where CURRENT OF cFKey" & vbCrLf strSQL = strSQL & " FETCH NEXT FROM cFKey" & vbCrLf strSQL = strSQL & " END" & vbCrLf strSQL = strSQL & "CLOSE cFKey" & vbCrLf strSQL = strSQL & "DEALLOCATE cFKey" & vbCrLf ' effect the row-by-row updating of the values to ignore those that cause index issues ' this will return false due to errors raised by objecting indexes ExecuteSPT strSQL End If |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-22 : 12:20:07
|
| Do you use this inside a production application? Has anyone ever told you about why cursors are bad and how to avoid them? Nigel, Rob, others, speak up here!Also, what is your temporary stored procedure doing that an already built stored procedure can't do? SQL Server allows dynamic sql, which is what you are doing.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-22 : 20:25:54
|
| I can't even figure out what you're trying to update in that bit. All I know is that CAN'T be the only way to do what you need. It's certainly NOT a good use of temporary stored procedures.You should also avoid using DAO with SQL Server entirely, it will always be a complete dog. There's nothing DAO could possibly do that you can't do more easily and efficiently with regular stored procedures executed via pass-through queries. |
|
|
|
|
Append character to begining and end of field valu