Update row with substring from another row.

Question

Update row with substring from another row.

igngua

SSCrazy

Points: 2510
More actions
October 5, 2009 at 3:29 pm

#211851

Hi! in this edition of noob-weekly-problem (:-D) i need to know how can i update a row with substring from another row.
example;
row1 row2
aaaaassssssss null
aaaaasssssss1 null
aaaaasssssss2 null
I want to update data in row2 taking everything from the first 's' to the right
How can i do this ??
thanks in advance.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Please show us what the expected results will be. A word description is okay, but some of us are more visual than others.

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 2

igngua (10/5/2009)
Hi! in this edition of noob-weekly-problem (:-D) i need to know how can i update a row with substring from another row.
example;
row1 row2
aaaaassssssss null
aaaaasssssss1 null
aaaaasssssss2 null
I want to update data in row2 taking everything from the first 's' to the right
How can i do this ??
thanks in advance.

UPDATE t SET

t.row2 = SUBSTRING(t.row1, CHARINDEX('s', t.row1), LEN(t.row1))

FROM dbo.table t

WHERE <condition if applicable>

Even though you're saying "rowXX", what I *think* you're trying to do is set one column equal to a value computed from another column in the same row.

Pro Tip: First, put the computed value into a SELECT statement (comment out my UPDATE statement and replace it with a SELECT and eliminate the assignment operators) to see what effect this will have on your data BEFORE committing the update 😉

MJM

igngua SSCrazy Points: 2510 More actions · Answer 3

Lynn Pettis (10/5/2009)
Please show us what the expected results will be. A word description is okay, but some of us are more visual than others.

ok.

the output:

row1----------- row2

aaaaassssssss ssssssss

aaaaasssssss1 sssssss1

aaaaasssssss2 sssssss2

igngua SSCrazy Points: 2510 More actions · Answer 4

Mark Marinovic (10/5/2009)
igngua (10/5/2009)
Hi! in this edition of noob-weekly-problem (:-D) i need to know how can i update a row with substring from another row.
example;
row1 row2
aaaaassssssss null
aaaaasssssss1 null
aaaaasssssss2 null
I want to update data in row2 taking everything from the first 's' to the right
How can i do this ??
thanks in advance.
UPDATE t SET
t.row2 = SUBSTRING(t.row1, CHARINDEX('s', t.row1), LEN(t.row1))
FROM dbo.table t
WHERE <condition if applicable>
Even though you're saying "rowXX", what I *think* you're trying to do is set one column equal to a value computed from another column in the same row.
Pro Tip: First, put the computed value into a SELECT statement (comment out my UPDATE statement and replace it with a SELECT and eliminate the assignment operators) to see what effect this will have on your data BEFORE committing the update 😉
MJM

thanks i´m going to try it.

about the pro tip; i always do that, 😀

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 5

Then you'll like this pro tip... what you are apparently calling rows are really columns.

--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)

igngua SSCrazy Points: 2510 More actions · Answer 6

Jeff Moden (10/5/2009)
Then you'll like this pro tip... what you are apparently calling rows are really columns.

my bad!