Skip to main content

Combine UPDATE and SELECT in one statement

After we did an UPDATE on a record and when we want to find out what are updated, we will usually follow up with a SELECT. Example,

update tblTest
set
    Age = Age + 1, LastModifiedDate = GETDATE()
where Name = 'Name 1'

select * from tblTest
where Name = 'Name 1'

How about doing the above all in one single statement and avoiding the select statement from getting outdated record when someone intercepts and attempts to update?

It's possible! Try the following.

--SETUP
if ISNULL(object_id('tblTest'),0)<>0 drop table tblTest
go
create table tblTest (
    ID int identity(1,1) not null primary key,
    Name varchar(255) not null,
    Age tinyint not null,
    LastModifiedDate datetime not null default getdate()
)
go

--INSERT test records
insert into tblTest (Name, Age) values ('Name 1', 21)
insert into tblTest (Name, Age) values ('Name 2', 22)
go

--CREATE the temp table for select
if ISNULL(object_id('tempdb..#tem_tblTest'),0)<>0 drop table #tem_tblTest
create table #tem_tblTest (ID int, Name varchar(255), Age tinyint)

--UPDATE record and select into temp table (All in one statement)
update tblTest
set
    Age = Age + 1, LastModifiedDate = GETDATE()
output inserted.ID, inserted.Name, inserted.Age into #tem_tblTest (ID, Name, Age)
where Name = 'Name 1'

--SELECT what's updated
select * from #tem_tblTest
go

--CLEANUP
if ISNULL(object_id('tempdb..#tem_tblTest'),0)<>0 drop table #tem_tblTest

Popular Posts

How to stop FortiClient from starting automatically?

Installed FortiClient recently but the challenge in disabling the application/service from running automatically on every start-up annoyed me. Attempt to stop 'FortiClient Service Scheduler' only return 'Parameter is incorrect' error message.

An article on Technet help solve my trouble. To stop FortiClient from starting automatically, try the following:
Shut down FortiClient from the system tray.
Run net stop fortishield on command prompt.
Run msconfig.
On msconfig, switch to the Services tab. Clear the FortiClient Service Scheduler check box and click Apply.Run services.msc on command prompt to open up show all available services.Look for FortiClient Service Scheduler. Switch Startup type to Manual.Restart your computer. FortiClient should not be running automatically the next time round. Hope it helps.

Ho Ching named 5th most powerful and is mistaken as first lady by Forbes

Forbes named Singapore Prime Minister wife and CEO of Temasek Holdings, Ho Ching, as the 5th most powerful woman in the world. Ho Ching is mistaken as Singapore's first lady! OMG!

I wonder how can Forbes makes such a blunder. For a complete list, refer to here.

Installing Oracle Complaining Missing dr0ulib.sql.sbs

When installing Oracle Database 12c Release 1, you may encounter an error complaining missing dr0ulib.sql.sbs file. You will be stuck no matter how many times you hit on Retry button. It just won't proceed!

To resolve this issue, you will need to do this:

Abort installation.Copy all folders and files in your \winx64_12102_SE2_database_2of2\database\stage\Components\ folder to \winx64_12102_SE2_database_1of2\database\stage\Components\.Initiate installation again.
Please do not ask me why the need for this silly step. It is just how Oracle package their installation files. I would recommend them doing a ISO file instead of doing 2 ZIP files!