Monday, August 9, 2010

Mouse Extender : A cozy utility


If you don’t like your desktop covered with so many icons and still want to open your favourite program with ease then Mouse Extender (ME) is the best option for you. I’ve been using it for quite a long time at my home as well as office. You can download it from CodePlex.

ME is based on WPF and requires .Net Framework 3.5 sp1 to be installed. If you’ve .Net framework installed then it’s good but if not then you can check minimum installation you need here.

You can configure it as per your need and if you want to know what features it provides then just go through this link.

Happy Programming :)

Tuesday, March 30, 2010

Update with join and unexpected output

Recently my one of the colleague was writing trigger and in that he’s having Update Query with join, syntax and everything was right but he’s not getting the expected output.  To understand the situation let’s take an example.

Create two temporary tables 
CREATE TABLE #tmpTable1
( gid smallint,
  gBal smallint
)
CREATE TABLE #tmpTable2
( tid smallint,
  tBal smallint
)
and Inset following values in both the tables.
 
insert into #tmpTable1(gid,gBal) values(1, 200), (2,300)
insert into #tmpTable2(tid,tBal) values(1, 300), (2,400),(1, 600), (2,700)

Now both the table contain inserted values:

SQL Insert Query Output
The update query with join that was not giving expected output :

update #tmpTable1 set gBal = gBal +  tbal
from #tmpTable1 inner join #tmpTable2 
on gid=tid 
 
As you can see nothing is wrong with the query, but it is updating it with the first two values of #tmpTable2 and hence the output will be as shown below but output should be 1100 for record no 1 and 1400 for record no 2.

Output

As per the MSDN Documentation: single UPDATE statement never updates the same row twice.

Let’s say that you have more than one sale  for a given item and you update as shown above, It does not work correctly. It executes without error, but each item is updated with only one sale, regardless of how many sales actually occurred on that day. In the situation in which more than one sale for a given title can occur on the same day, all the sales for each title must be aggregated together within the UPDATE statement, as shown in following query:

Update #tmpTable1
SET gBal = gbal +
(SELECT SUM(tbal)
FROM #tmpTable2 AS tmp
WHERE tmp.tid = gid)
 
This will give us the expected output that is 

Output With Update Query and Join That’s it!!  Enjoy ;)