Who is a set-based master? - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Who is a set-based master?

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-26 : 10:28:19

This gives me the error: The table 'Things' is ambiguous.
I'm trying to update the the ParentID on a recursive table

UPDATE Things
SET ParentThingID = ThingID
FROM Things C
INNER JOIN Things P ON C.Old_ParentThing = P.Old_Thing

Why wont this work?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-26 : 10:34:27

Try


UPDATE C
SET C.ParentThingID = P.ThingID
FROM Things C
INNER JOIN Things P ON C.Old_ParentThing = P.Old_Thing

Andy

Beauty is in the eyes of the beerholder

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-26 : 10:45:24

Cheers Andy! I should have thought of that really
funny how that works
If the query i posted wasnt working with a recursive table, it would have worked fine

Billpl
Yak Posting Veteran

71 Posts

Posted - 2005-04-27 : 15:34:41

damn, yet another little thing I didn't know that would have come in handy, (about 1/2 a billion times!)...thx andy

wondering, is that syntax noted in BOL anywhere and I just missed it?
wonder if there's a book or article of "top 50 quirky little things about SQL that you should know"

another good example I picked off this site that I haven't seen anywhere else.

/*-----------------------------------------------------------------------------
Update Set Up Running Total on Balance
-----------------------------------------------------------------------------*/
Declare @Balance Decimal(15,2)
Select @Balance = 0
Update @DetailTable Set @Balance = Balance = @Balance + Amount

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-27 : 18:20:17

Well, that's documented in Books Online too.

Best way to read Books Online: go to the index, start at the top, open each item one at a time and read them all. Do the same thing with the Table of Contents.

Billpl
Yak Posting Veteran

71 Posts

Posted - 2005-04-28 : 14:25:56

Well, lo and behold I did a search on "Update Alias" in BOL and the very first item, under the 'installing SQL' topic, had an example,

Though, I looked at the UPDATE topic itself and it does not show the ALIAS option in the syntax definition. Below is part of the Topic that could actually steer some in the wrong direction.
------------------------------------------------------
A table alias specified in a FROM clause cannot be used as a qualifier in SET column_name. For example, this is not valid:

UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

To make the example work, remove the t. alias from the column name.

UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
------------------------------------------------------
And the other thing IS there. (missed it completely)
------------------------------------------------------
@variable
Is a declared variable that is set to the value returned by expression.

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column

------------------------------------------------------

all in all I can't complain, the SQL BOL is a great reference guide, much better than most. SQL is classic of the old saying "the more you know, the more you realize how little you know"...err something like that

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-05-13 : 11:59:33

Well, it's been a while guys but it's happened again.
I can't see how to do this without a cursor. Who's the Set-based master?
I have a table with a uniqueidentifier column (C) and an int column (A).
Some values in Column A are null and I need to set them to unique incremental int values (what identity constraint would have done if it was set at table creation).
Here is how the cursor solution does it:

DECLARE @GUID UNIQUEIDENTIFIER
DECLARE GUID_Cursor CURSOR FOR
SELECT C FROM NickTest WHERE A IS NULL
OPEN GUID_Cursor
FETCH NEXT FROM GUID_Cursor
INTO @GUID
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @RecPoin INT
SET @RecPoin = (SELECT TOP 1 a from NickTest ORDER BY a DESC)

UPDATE NickTest SET A = @RecPoin + 1 WHERE C = @GUID

FETCH NEXT FROM GUID_Cursor
INTO @GUID
END
CLOSE GUID_Cursor
DEALLOCATE GUID_Cursor
GO

And the set-based statement does it how?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-13 : 13:36:37

Master based set?

Brett

8-)

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-05-13 : 13:38:40

dont get it

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-13 : 13:48:17

>>Master based set?
I think it has something to do with hand calluses...

Here's one way to duplicate your cursor code without the loop


declare @a int
select @a = max(a) from nicktest

update nicktest set
	@a = @a + 1
	,a = @a
where 	a is NULL

Be One with the Optimizer
TG

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-05-17 : 03:52:02

thanks TG. That worked brilliantly.
Get a room Brett

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-05-19 : 04:15:07

Along similar lines. I have a table with a GUID column and a datetime column. This time I want to update the date column so that each record in the table has a unique datetime value. But if I use the same approach, it runs so quick that I have duplicate datetimes. Can you crack that one TG?

/** sql sui generis **/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-19 : 07:59:32

Sounds easy if you're just manufacturing fake dates. Do you you have some guidelines about what value the datetime should be. If it's really "along similar lines", then any random datetime would work. based on your comment about the speed of the query, sounds like you were using getdate(). What business meaning does the date serve?


declare @d datetime
select @d = convert(datetime,0)

update nicktest set
	@d = dateAdd(millisecond, 10, @d)
	,d = @d
where 	d is NULL

Be One with the Optimizer
TG

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-05-19 : 10:34:25

I was using getdate(). I'm testing how well my table can cope under a heavy load and I want to put a clustered index on the date. But if all the dates are the same, the query optimizer wont be helped very much if the sample it takes has all the date values the same. Once again, your solution is pure genious.
If you're interested further in what I am doing, check this out:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48154&whichpage=2

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-07-20 : 11:57:10

Along similar lines:

I have a main table with a join table coming off of it for a one-to-many relationship.
For each record in the "many" table, I have to increment a INT column - strating at 1. So each record in the main table will have each of it records in the join table numbered and starting from 1. Currently the INT column has NULL in it for all records. So the desired result would be (for simplicity I have illustrated with INT key columns, but it's actually GUID's):
MAIN Table
ID
1
2

JOIN Table
ID LINK_ID VALCOL
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3

There are 20000 records in the "join" table and 70000 records in the main table (many records in the main table do not have any records in the join table). All I could think of was a loop. But it's been running for over half an hour! This is how I have attempted to do it:

CREATE PROCEDURE [dbo].[p_tmp_Update]
@tmpID UNIQUEIDENTIFIER
AS
DECLARE @A INT
SET @A = 0

UPDATE T
SET
@A = @A + 1,
T.ValCol = @A
FROM JoinTable T
INNER JOIN MainTable S ON S.ID = T.Link_ID
WHERE S.ID = @tmpID
GO

DECLARE @A UNIQUEIDENTIFIER
SET @A = (SELECT TOP 1 Link_ID FROM JoinTable WHERE ValCol IS NULL)

WHILE @A IS NOT NULL
BEGIN
EXEC p_tmp_Update @A
SET @A = (SELECT TOP 1 Link_ID FROM JoinTable WHERE ValCol IS NULL)
END

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-20 : 18:39:14

quote:
JOIN Table
ID LINK_ID VALCOL
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3

You can't post that without justifying why You have to do that

What is the business need to order the rows internally within the ID LINK_ID groups ???
Specifically when You state that ID + LInK_ID are UNIQUEIDENTIFIERS, I fail to understand what the VALCOL means or what value it provides?

How does this run (UPDATE omitted) ?

SELECT L1.ID, L1.LINK_ID, COUNT(*) AS VALCOL
FROM
	<JOINTable> L1 JOIN <JOINTable> L2
		ON L1.LINK_ID = L2.LINK_ID
		AND L1.ID >= L2.ID
GROUP BY
	L1.ID, L1.LINK_ID
ORDER BY
	1,2

rockmoose

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-07-26 : 04:43:23

"You can't post that without justifying why You have to do that
What is the business need to order the rows internally within the ID LINK_ID groups ???
Specifically when You state that ID + LInK_ID are UNIQUEIDENTIFIERS, I fail to understand what the VALCOL means or what value it provides?"
It's not that I need to order them as such. The users need a human-readable number that they can make reference to. The request has been that this number should be the combination of the human-readable number in the main table and the number in the table that joins off of it. There will never be more than 1000 records in the joining table that link to one record in the main table.

Your query works perfectly. I just swapped the order by columns around so I could see the groupings together.
I've seen the ">=" syntax once or twice before. Could you have written this query without using it? Where can I read more about this syntax? (It's not the easiest thing to google or bol). Is it a left join?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-07-26 : 04:55:26

I made the UPDATE Statement like this:

UPDATE T
SET T.VALCOL = S.VALCOL
FROM JOINTable T
INNER JOIN
(SELECT TOP 100000 L1.ID, L1.LINK_ID, COUNT(*) AS VALCOL
FROM
<JOINTable> L1 JOIN <JOINTable> L2
ON L1.LINK_ID = L2.LINK_ID
AND L1.ID >= L2.ID
GROUP BY
L1.ID, L1.LINK_ID
ORDER BY
2,1) AS S
ON T.ID = S.ID

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-26 : 05:10:38

It's a non-equi join.
">=" this is just the regular "larger than or equal" syntax.
(There is some info in BOL, if You look under the topic "equi-join")

You can use all the operators such as =, >, >=, < , <= , IN , BETWEEN in the JOIN clause.

You put together the UPDATE clause much the way I would.
I don't understand why You have the TOP clause, If I were to restrict the # of rows updated I would use a WHERE clause
in the derived table (S).

Don't You need:
ON T.ID = S.ID
AND T.LINK_ID = S.LINK_ID -- no ???

rockmoose

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-07-26 : 05:55:38

Thanks for that. I'll remind myself about equi-joins. I think I was over-analyzing what you were doing! :-)

If you take the TOP out you get:
Server: Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
I cant see why I would need "AND T.LINK_ID = S.LINK_ID ". The ID column is already unique and thus it already takes me to the one record I am looking for.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-26 : 06:14:58

Ah, yes, that's right.
But the ORDER BY is not needed, I just put it there for display purposes.
Did not think of mentioning that.

rockmoose

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources