Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2011-01-11 : 14:00:59
|
| Hi all,I need to concatenate the suffix of a field to another. For instance, data looks like this.612900 | 1234-00612900 | 1234-01The problem is that there are duplicate records. I don't believe I can use a 'where' clause to identify the row for it may get the wrong one. Is there a way to identity the CURRENT active record within the recordset? I'm using a cursor to loop thru all records within the table.I need the table field to come out in this format:612900-00612900-01Thanks for your help. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-11 : 14:29:33
|
| SELECT col1 + RIGHT(col2, 3) FROM yourTableUnless you are joining something on col1, this will work. And if you are, show what you have so far and we'll help you out. |
 |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-11 : 14:31:27
|
| Thanks, but I actually need to update the field in the database. Not simply run a query. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-11 : 14:46:13
|
| UPDATE yourTable SET col1 = col1 + RIGHT(col2, 3) -- if they are both varchar.If col1 is an int:UPDATE yourTable SET col1 = Convert(varchar(32), col1) + Right(col2, 3).Both assume col2 has a hyphen in it, as in your sample data. If not thenCol1 + '-' + Right(col2, 2)All of these will update the correct row, SQL won't match row1 to row2 unless you're performing a join. |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-11 : 14:58:57
|
| Dude, that is awesome. I was worried that such a statement would update every row within the table and not each individually so I ran a test and it worked.Thanks a lot. You don't know how much you've helped me. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-11 : 15:20:58
|
| You're welcome. Glad it worked out for you. |
|
|
|
|
|
|
|
|
Concatenate two columns involving duplicate rows