Data Manipulation - Insert, Update, Delete

Data Manipulation - Insert, Update, Delete

// INSERT

custTable custTable;
;
custTable.accountNum ="1234";
custTable.name ="John Customer";
custTable.insert();
_________________________________________________________________________________

//UPDATE

SalesTable salesTable;
;
ttsbegin;
while select forupdate salesTable
where salesTable.CustAccount =="2001"
{
salesTable.SalesName ="New Enterprises";
salesTable.update();
}
ttscommit;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


InventTable InventTable;
;
ttsbegin;
while select forupdate InventTable
where InventTable.ItemGroupId =="Television"
{
InventTable.ItemPriceToleranceGroupId ="2%";
InventTable.update();
}
ttscommit;
Microsoft

_________________________________________________________________________________

//DELETE

CustTable custTable;
;
ttsbegin;
Select forUpdate custTable
where custTable.accountnum =="2032";
custTable.delete();
ttscommit;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
//DELETE_from

CustTable custTable;
;
delete_from custTable
where custTable.AccountNum =="4018";

While Select

CustTable custTable;
while select accountNum, name, address from custTable
{
print custTable.AccountNum, " ", custTable.Name, " ",
custTable.Address;
pause;
}
_________________________________________________________________________________

CustTable custTable;
;
while select custTable
where custTable.AccountNum > "4005"
&& custTable.AccountNum < "4017"
{
print custTable.AccountNum , " ",custTable.Name;
}
pause;
_________________________________________________________________________________
//Sorting Obtion index

CustTable custTable;
;
while select custTable index AccountIdx
{
print custTable.AccountNum, " ", custTable.Name;
}
pause;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 //ORDER BY


CustTable custTable;
;
while select custTable order by AccountNum desc
{
print custTable.AccountNum, " ", custTable.Name;
}
pause;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

//GROUP BY
SalesTable salesTable;
;
while select count(salesid) from salesTable group by
CustGroup
{
print salesTable.CustGroup," ",salesTable.salesid;
}
pause;

_________________________________________________________________________________

//Aggregate
sum -->Returns the sum of the values in a field.

avg -->Returns the average of the values in a field.

maxof -->Returns the maximum of the values in a field.

minof -->Returns the minimum of the values in a field.

count -->Returns the number of records that satisfy the statement. Count can only be used on numerical fields, since the result will be returned in that field.

select sum(amountMST) from ledgerTrans;
sumAmountMST = ledgerTrans.amountMST;
select count(recId) from ledgerTrans;
countLedgerTrans = ledgerTrans.recId;