update table after ;with() select statement

Question

I use this procedure to select records with paging :

ALTER PROCEDURE [PorSun].[soalPagingByIdGroupIdUser]  ( 
   @IdGroup , 
   @pageNo int, 
   @pageSize int)  
AS 
   DECLARE @start int, @end int 
   SET @start = @pageSize*(@pageNo-1)+1 
   SET @end= @start+@pageSIze-1

   ;with Records as
   (
       SELECT 
          PorSun.soal.Id, PorSun.soal.IdUser, PorSun.soal.VisitCount
          , ROW_NUMBER() over(order by PorSun.soal.Id) as RN
       FROM PorSun.soal 
       WHERE (PorSun.soal.IdGroup=@IdGroup)
   )
   SELECT Records.Id, Records.IdUser, Records.VisitCount
   FROM Records
   WHERE RN between @start and @end and (Records.IdGroup=@IdGroup)
   ORDER BY Records.Id desc

   UPDATE [PorSun].[Soal]
   SET [VisitCount] = [VisitCount]+1
   WHERE Id IN (SELECT Id FROM Records)

There is no syntax error, but on execute error.

How is it possible?

`Records` view is visible only in the FIRST query (SELECT,UPDATE,...) after CTE. — valex, Jun 04 '14 at 13:30
use an UPDATE to increment the VisitCount, and within that, use and OUTPUT clause to return the result set you need to display them — KM., Jun 04 '14 at 14:24
@KM. Your offer is interesting: use OUTPUT clause, may you give an example or a link. It sounds good. — Ahmad Ebrahimi, Jun 26 '14 at 05:52
this will update and return a result set: `UPDATE YourTable SET YourCol1='xyz' OUTPUT INSERTED.YourPK, INSERTED.Col1, DELETED.Col1 AS OldCol1 WHERE ...` — KM., Jun 26 '14 at 15:32

score 0 · Accepted Answer · answered Jun 04 '14 at 14:26

0

You can use a table variable instead of a CTE:

DECLARE @Records TABLE
(
   Id         int,
   IdUser     int,
   VisitCount int,
   RN         int
)

INSERT INTO @Records
SELECT 
    PorSun.soal.Id, 
    PorSun.soal.IdUser, 
    PorSun.soal.VisitCount,
    ROW_NUMBER() over(order by PorSun.soal.Id) as RN
FROM PorSun.soal 
WHERE (PorSun.soal.IdGroup=@IdGroup)


SELECT Id, IdUser, VisitCount
FROM @Records 
WHERE RN between @start and @end and (IdGroup=@IdGroup)
ORDER BY Id desc

UPDATE [PorSun].[Soal]
SET [VisitCount] = [VisitCount]+1
WHERE Id IN (SELECT Id FROM @Records)

answered Jun 04 '14 at 14:26

D Stanley

149,601
11
178
240

you can do a select and an update in a single statement: UPDATE+OUTPUT clause. Here is an old answer where I show how you can delete+insert+select in a single statement: http://stackoverflow.com/a/2829592/65223 – KM. Jun 04 '14 at 14:27
The `SELECT` has a different `WHERE` clause than the `UPDATE`, so I don't think it can be used here. I've retracted that part of my answer, though. – D Stanley Jun 04 '14 at 14:30
I think the intent is to increment the rows that are displayed, but that is for the OP to decide – KM. Jun 04 '14 at 14:34
Thanks all. Yes, I wanted to update selected records vizit count. As I have found out it is not possible in one statement, so I used a second statement for update. I used ;with clause for paging, and I thought it is the best way...??? – Ahmad Ebrahimi Jun 26 '14 at 05:40
It's _one_ way - It's basically syntax for a reusable sub-query (and gets compiled as such). A temp table or table variable work too (and sometimes perform better). – D Stanley Jun 26 '14 at 12:36

update table after ;with() select statement

1 Answers1