CODEWITHSUNDEEP
sql-servert-sql
ZeRaTuL_jF
ZeRaTuL_jF

Reputation: 592

Field is being updated with same value

I have a table that has a new column, and updating the values that should go in the new column. For simplicity sake I am reducing my example table structure as well as my query. Below is how i want my output 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 my table and it looks as such

      IDNumber NewColumn
        1         1
        2         1
        3         1
        4         1
        5         1

However when I do the select only I can see that I get the desire output, now I have done this before to split result sets into multiple groups and everything works within the select but now that I need to update the table I am getting this weird result. Not quiet sure what I'm doing wrong or if anyone can provide any sort of feedback.

So after a whole day of frustration I was able to compare this code and table to another that I had already done this process to. The reason that this table was getting updated to all 1s was because turns out that whoever made the table thought this was supposed to be a bit flag. When it reality it should be an int because in this case its actually only two possible values but in others it could be more than two. Thank you for all your suggestion and help and it should teach me to scope out data types of tables when using the ntile function.

Upvotes: 3

Views: 62

Answers (1)

dybzon
dybzon

Reputation: 1604

Try updating your table directly rather than updating your CTE. This makes it clearer what your UPDATE statement does.

Here is 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 assume that what you want to achieve is to group your records into two randomized partitions. This statement seems to do the job.

Upvotes: 1

Related Questions