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.

 All Forums
 Other Forums
 MS Access
 Wow or Ugh? Jet SQL vs T-SQL

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.age
SET 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.
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-13 : 20:28:33
Oh... is this trick possible in T-SQL???
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-13 : 20:41:58
There's a similar feature available in T-SQL:

http://www.sqlteam.com/item.asp?ItemID=765

It may work with columns as well as variables, but I haven't tested it. I have used variations of it though:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19066
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24795
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-14 : 04:40:28
Well, I know this plain sample:

declare @intCounter int
set @intCounter = 0
update Yaks
SET @intCounter = YakSequenceNumber = @intCounter + 1

My 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.nick
from t2 inner join t1 on t2.age=t1.age

Now t2.nick='ff', NOT 'ffgghh'!!

Plus, (1 row(s) affected)...
But MS Access warning message is "... 3 row(s) will be updated..."
Go to Top of Page

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.
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-14 : 18:36:12
Starting this thread I meant to discuss the wonderful trick of
Jet SQL to update ONE row for several times within ONE
Update Statement.
Obviously I'm not seeking for any T-SQL methods of
concatenating field values.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -