Update from one Table to another based on a ID match

IN SQL SERVER

UPDATE DETAILSTABLE
SET DETAILSTABLE.TestCol = M.TestCol
FROM DETAILSTABLE D INNER JOIN MASTERTABLE M
ON D.Id = M.Id

IN ORACLE

UPDATE DETAILSTABLE
SET DETAILSTABLE.TestCol = ( select MASTERTABLE.TestCol
from MASTERTABLE
where DETAILSTABLE.Id = MASTERTABLE.Id)
where exists (select 1
from MASTERTABLE
where DETAILSTABLE.Id = MASTERTABLE.Id)

Thanks – JOxin

Remove duplicates rows in a datatable

The sample code below demonstrates how to remove duplicate rows in a datatable.

DataTable dt = new DataTable();
dt.Columns.Add(“id”, typeof(int));
dt.Columns.Add(“name”, typeof(string));

dt.Rows.Add(1, “test 1″);
dt.Rows.Add(2, “test 2″);
dt.Rows.Add(3, “test 3″);
dt.Rows.Add(4, “test 4″);
dt.Rows.Add(4, “test 4″);
dt.Rows.Add(5, “test 5″);
dt.Rows.Add(5, “test 5″);

DataView view = new DataView(dt);
DataTable distinctValues = view.ToTable(true, “id”, “name”);

Hope this helps.

Thanks – JOxin

Convert Unix timestamp to Local Time

The Unix timestamp is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT),

The below method can be used to convert Unix timestamp to Local Time

static DateTime ConvertFromUnixTimestamptoLocalTime(double timestamp)
{
DateTime baseDate = new DateTime(1970, 1, 1, 0, 0, 0, 0);
return baseDate.AddMilliseconds(timestamp).ToLocalTime();
}

eg-:

double Unixtimestamp = 1331124651312;
DateTime printDate = ConvertFromUnixTimestamptoLocalTime(Unixtimestamp);

Thank you -Joxin

Insert special character ‘&’ into oracle database

& is the substitution symbol in oracle. Two solutions i have got from web is listed below

1) by setting ‘define‘ keyword off

Example-:

SQL> set define off
SQL> insert into student values('S001','John','Terry & Marry');

2) by using escape character \ just before &

Example-:

SQL> insert into student values('S001','John','Terry \& Marry');

Thank you -Joxin