Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

mssql has a lot of "tricks" associated with CTE's. Using the OUTPUT clause on updates is one of them.. Here's some queue code using a cte to grab the top one by queued date.

  DECLARE @Queue TABLE (QueueID INT)

  WITH q AS (
  		SELECT TOP (1) QueueID, StatusID, ModifiedDate
		FROM worker.Queue WITH (ROWLOCK, READPAST)
		WHERE StatusID=(SELECT TOP 1 s.StatusID FROM worker.Status s (NOLOCK) WHERE s.[Status] IN ('Queued'))
		ORDER BY QueueDate ASC
  	)
  	UPDATE q SET 
		StatusID=(SELECT s.StatusID FROM worker.Status s (NOLOCK) WHERE s.[Status]='Processing'), 
		StatusMessage='New Process',
		ModifiedDate=GETDATE()
	OUTPUT INSERTED.QueueID INTO @Queue


The OUTPUT clause in MSSQL doesn't require a CTE, and can be used with INSERT, DELETE and MERGE in addition to UPDATE.

Likewise, your example can also be done with derived tables, which predate CTEs in MSSQL:

    UPDATE a SET col2 = 10
    OUTPUT INSERTED.key1 INTO @local_table (key1)
    FROM (SELECT TOP (1) * FROM table1 ORDER BY col1) a


Looks like Postgres' RETURNING clause... The latter works with inserts and updates, too, e.g.:

    with foo as (
    insert ... values ...
    returning *
    ),
    bar as (
    insert ... select ... from foo
    returning *
    )
    select ... from foo, bar, baz ...




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: