[SOLVED] SQL update using OUTPUT clause

I'm trying to use the OUTPUT clause here https://msdn.microsoft.com/en-us/library/ms177564.aspx

What I'm doing is updating a column that contains a key for a record.  So say the key is 10.  I need to update it to 11 and return that 10.  This can be accomplished by using a simple update query but codeigniter isn't liking it and won't return any results even though it does complete.

This query does not return any results and I get an error trying to use result_array(), result(), or row()

Code:

UPDATE
  KEY_COUNTERS
SET
  KEYS_COUNTERS = KEYS_COUNTERS + 1
OUTPUT
  deleted.KEYS_COUNTERS
WHERE
  KEY_COUNTERS_ID = 'KEY'

This query runs as well and I can run result_array() and get the deleted key but I don't want to have to run a select and then update.

Code:

DECLARE @KEY INT;

SELECT
  @KEY = KEYS_COUNTERS+1
FROM
  KEY_COUNTERS
WHERE
  KEY_COUNTERS_ID = 'KEY'

UPDATE
  KEY_COUNTERS
SET
  KEYS_COUNTERS = @KEY
OUTPUT
  deleted.KEYS_COUNTERS
WHERE
  KEY_COUNTERS_ID = 'KEY'

Can anyone tell me why the first query doesn't return results but the bottom one does.  If I run this query in SQL management studio I get the same result so only codeigniter isn't working.  The only thing I can think is that since the first query only has the Update query that it doesn't return results because of codeigniter.

Well I guess in case someone else has a similar issue. I dug into the framework code and documentation found out that you can force the query to return the Result Object.

https://www.codeigniter.com/userguide3/d...ver::query

so when running the function query() you can do the following query($sql, array(), TRUE) and no matter what you will get the result object which is what I needed.