June 10, 2015 at 9:31 am
Consider the below script
CREATE TABLE #TEMP(Id int,CreatedBy varchar(30),ModfiedBy varchar(30))
CREATE TABLE #TEMP2 (ID int,SearchedBy varchar(30))
INSERT INTO #TEMP VALUES(1,'James',NULL)
INSERT INTO #TEMP VALUES(1,'James','George')
INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
SELECT CASE WHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
WHERE #TEMP2.ID=CTE.ID
Only the first record gets updated... 🙁
June 10, 2015 at 9:46 am
Should all the records have an ID of 1? As you are then using that ID to join, as far as I can see, you will have 3 rows returning the same
1 James
Bex
June 10, 2015 at 9:47 am
All the records of #Temp2 were updated when I ran your scripts.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 10, 2015 at 10:00 am
There are three main issues here:
1) You have no join clause in your query, so you are essentially doing a CROSS JOIN.
2) Neither table has a primary key, so you cannot uniquely identify a record in either table. This means that when you do add a join, you will still have a (partial) CROSS JOIN.
3) You are ordering your row_number by the same field as you are partitioning by. This means that the order of rows within the partition is not guaranteed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 10, 2015 at 10:09 am
Your syntax is a little out:
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2 CTE
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
WHERE #TEMP2.ID=CTE.ID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 10, 2015 at 11:00 am
ChrisM@Work (6/10/2015)
Your syntax is a little out:
;WITH CTE AS(SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE
#TEMP2CTESET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTEWHERE #TEMP2.ID=CTE.ID
I thought that at first, but SearchedBy is on #TEMP2, so it seems that he wants to update #TEMP2 based on #TEMP.
It turns out that he is using an implicit old-style join on the two tables. The correct syntax is below.
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM #TEMP2
INNER JOIN CTE
ON #TEMP2.ID=CTE.ID
As I said before, because there is no primary key on either table, he is essentially doing a CROSS JOIN.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 10, 2015 at 11:40 am
Yes, i am trying to update #temp2 , so is there a way to achieve this 🙁 ...
June 10, 2015 at 11:59 am
If #Temp2 had a RowNumber column, with values that reset to 1 for each grouping, then in your update statement, you can add the join on the row number to the existing condition. I think that might work.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 11, 2015 at 1:56 am
drew.allen (6/10/2015)
ChrisM@Work (6/10/2015)
Your syntax is a little out:
;WITH CTE AS(SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE
#TEMP2CTESET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTEWHERE #TEMP2.ID=CTE.ID
I thought that at first, but SearchedBy is on #TEMP2, so it seems that he wants to update #TEMP2 based on #TEMP.
It turns out that he is using an implicit old-style join on the two tables. The correct syntax is below.
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM #TEMP2
INNER JOIN CTE
ON #TEMP2.ID=CTE.ID
As I said before, because there is no primary key on either table, he is essentially doing a CROSS JOIN.
Drew
Just before I posted that, a brief message flashed past...
"Unfortunately, Thinking has stopped"
Thanks Drew.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2015 at 8:37 am
Alvin Ramard (6/10/2015)
If #Temp2 had a RowNumber column, with values that reset to 1 for each grouping, then in your update statement, you can add the join on the row number to the existing condition. I think that might work.
Hi Alvin,
Following your advice of one more column as rownum in #temp2 , i added it but now i am stuck at another deadend.
ALTER TABLE #TEMP2
ADD RowNumber int
UPDATE #TEMP2
SET RowNumber = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID ASC)
I google for the error message but the solution given in StackOverflow and SQLServerCentral does not work with my case.
Any help on this.
June 11, 2015 at 8:48 am
Shadab Shah (6/11/2015)
Alvin Ramard (6/10/2015)
If #Temp2 had a RowNumber column, with values that reset to 1 for each grouping, then in your update statement, you can add the join on the row number to the existing condition. I think that might work.Hi Alvin,
Following your advice of one more column as rownum in #temp2 , i added it but now i am stuck at another deadend.
ALTER TABLE #TEMP2
ADD RowNumber int
UPDATE #TEMP2
SET RowNumber = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID ASC)
I google for the error message but the solution given in StackOverflow and SQLServerCentral does not work with my case.
Any help on this.
What's the error message?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 11, 2015 at 8:59 am
Shadab Shah (6/11/2015)
Alvin Ramard (6/10/2015)
If #Temp2 had a RowNumber column, with values that reset to 1 for each grouping, then in your update statement, you can add the join on the row number to the existing condition. I think that might work.Hi Alvin,
Following your advice of one more column as rownum in #temp2 , i added it but now i am stuck at another deadend.
ALTER TABLE #TEMP2
ADD RowNumber int
UPDATE #TEMP2
SET RowNumber = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID ASC)
I google for the error message but the solution given in StackOverflow and SQLServerCentral does not work with my case.
Any help on this.
The purpose of adding the row number to #TEMP2 is so that you can uniquely identify each row in #TEMP2. If your data supports a natural unique key, it is better to use that than to artificially create one with ROW_NUMBER().
If you are getting an error message, it really, really, REALLY helps if you tell us what error message you are getting.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 11, 2015 at 10:58 pm
Below is the error
Msg 4108, Level 15, State 1, Line 2
Windowed functions can only appear in the SELECT or ORDER BY clauses.
June 12, 2015 at 7:39 am
Shadab Shah (6/11/2015)
Below is the error
Msg 4108, Level 15, State 1, Line 2Windowed functions can only appear in the SELECT or ORDER BY clauses.
The way to get around this is to place the windowed function in a CTE and then update the CTE.
;
WITH t2 AS (
SELECT ID, RowNumber,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID ASC) AS rn
FROM #TEMP2
)
UPDATE t2
SET RowNumber = rn;
You should also note that all rows within a particular partition have the same values for all of the columns specified in the PARTITION BY clause, so including any of those columns in your ORDER BY clause accomplishes nothing.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply