with A as
(
   select 
      row_number() over (partition by articleid order by categoryid) as row,
      primarycat
   from
      article_category
)
update A set primary_cat = 1 where row = 1

quote:

---

Originally posted by jwallz

Hi

I have a link table to store categories assigned to articles. Currently it's in this form
Table: Article_Category
Columns: ArticleID (pk, fk, int, notnull)
CategoryID (pk,fk,smallint,notnull)

An article can have 1+ associated categories. What i need to do is set one primary category for each ArticleID. So I'm adding a bit column to this table called PrimaryCat(bit, notnull, default 0)

Now I'd like to arbitrarily set a primary cat for each of the unique ArticleIDs.

if a particular ArticleID is associated with 4 categories i'd like to randomly set one as the PrimmaryCat, ie:

ArticleID CategoryID PrimaryCat
10 22 0
10 23 1
10 24 0
10 25 0

I'm having trouble creating the update statement to do this because i don't know how to identify the where clause. My issue is the update is dependent on a combo of both ArticleID and CategoryID. There's no primary key since this is a link table. I'm stuck here:
UPDATE Article_Category
SET PrimaryCat = 1
WHERE ResourceID = ?? and CategoryID = ??

---

UPDATE t
SET t.PrimaryCat=1
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY ArticleID ORDER BY NEWID()) AS Seq,PrimaryCat
FROM Article_Category
)t
WHERE Seq=1