The field is updated with the same value
I have a table that has a new column and is updating the values that should go in the new column. For the sake of simplicity, I am reducing the structure of the examples table as well as my query. Below is how I want my result to look.
IDNumber NewColumn
1 1
2 1
3 1
4 2
5 2
WITH CTE_Split
AS
(
select
*,ntile(2) over (order by newid()) as Split
from TableA
)
Update a
set NewColumn = a.Split
from CTE_Split a
Now when I do this I get the table and it looks like this
IDNumber NewColumn
1 1
2 1
3 1
4 1
5 1
However, when I only make a selection, I see that I get a wish message, now I have done this before to split the result sets into multiple groups and everything works within the selection, but now I need to update the table I am getting this strange result. Not sure what I am doing wrong or if anyone can provide any feedback.
So, after a day of frustration, I was able to compare this code and table with another one to which I had already followed this process. The reason this table was updated to all 1s was because whoever made the table thought it should be a bit flag. When this is reality, it should be an int, because in this case there are actually only two possible values, and in others there are more than two. Thank you for all your suggestions and help, and he should teach me how to expand table data types when using the ntile function.
source to share
Try updating the table directly instead of updating the CTE. This makes it clearer what your UPDATE statement is doing.
Here's an example:
WITH CTE_Split AS
(
SELECT
*,
ntile(2) over (order by newid()) as Split
FROM TableA
)
UPDATE a
SET NewColumn = c.Split
FROM
TableA a
INNER JOIN CTE_Split c ON a.IDNumber = c.IDNumber
I am assuming that you want to group your posts into two randomized sections. This expression seems to do the job.
source to share