Author |
Topic |
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-09-13 : 19:27:33
|
[code]t1 t2------------- --------------age nick age nick------------- --------------22 ff 22 22 gg --------------22 hh-------------[/code]UPDATE t2 INNER JOIN t1 ON t2.age=t1.ageSET t2.nick = (t2.nick + ", ") & t1.nick;Now t2.nick = "ff, gg, hh".3(!!!) rows affected. |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-13 : 20:09:45
|
3 rows were returned by the JOIN condition, so the rowcount is correct.Not sure I see what you mean about Jet and T-SQL though. |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-09-13 : 20:28:33
|
Oh... is this trick possible in T-SQL??? |
 |
|
robvolk
Most Valuable Yak
15732 Posts |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-09-14 : 04:40:28
|
Well, I know this plain sample:declare @intCounter intset @intCounter = 0update YaksSET @intCounter = YakSequenceNumber = @intCounter + 1My case is essentially different... OK, let's try this T-SQL query:declare @s varchar(50) set @s=''update t2 set @s=t2.nick=@s+t1.nickfrom t2 inner join t1 on t2.age=t1.ageNow t2.nick='ff', NOT 'ffgghh'!!Plus, (1 row(s) affected)...But MS Access warning message is "... 3 row(s) will be updated..." |
 |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-14 : 17:24:34
|
The update to concatenate rows only works when you are setting a variable.You could use a function to concatenate into a variable for the id and return the string which is then used in the update.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-09-14 : 18:36:12
|
Starting this thread I meant to discuss the wonderful trick ofJet SQL to update ONE row for several times within ONEUpdate Statement.Obviously I'm not seeking for any T-SQL methods ofconcatenating field values. |
 |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-14 : 20:27:01
|
????>> Oh... is this trick possible in T-SQL???And the answer is yes by the use of a function.You won't get the same rowcount as in access but that's just due to the way the statements are executed.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-09-15 : 09:07:33
|
>And the answer is yes by the use of a function.I meant exactly the same (maybe with minor variations)SQL construction as I used in Jet SQL.I don't count involving a function as a minor alteration. |
 |
|
|