SQL Server

Working With Information Within The Tables / Relations – Part 3

Working With Information Within The Tables / Relations Part – 3

Altering Information In The Table / Relation

An individual is required to alter the information in the database as soon as the disclaimers of a customer preserved through the business experiences an alteration.

Think through an instance, when a customer of the XYZBank alters his / her phone number or the email ID of some other customer is altered, the essential modifications desires to be made to the corresponding rows or tuples in the tables or attributes. An individual can make use of the UPDATE command to make the modifications. Bring up to date information in effect guarantees that the most recent as well as right data is offered every time. One column or attribute of a row or tuple is the minutest part of information that an individual can update in any table or relation. An individual can alter the information in a table or relation by means of the UPDATE Data Modification Language (DML) command. The code of the UPDATE command is as follows:

UPDATE My_Table_Name SET My_Column_Name = Data [ , My_Column_Name = Data ] [ FROM My_Table_Name ] [ WHERE Clause ]

here,

· My_Table_Name – It stipulates the name of the table or relation that an individual have to update.

· Column_Name – It stipulates the columns or attributes name that an individual have to change in the stated table or relation.

· Data – It stipulates the data value / values by which an individual have to alter the column / columns or attribute / attributes of the table or relation. A number of valid data values are consist of a column or attribute name, an expression or a variable name. The DEFAULT as well as NULL keywords can be used in this command also.

· FROM My_Table_Name – It stipulates the table / tables or relation or relations which are / are castoff in the UPDATE command.

· Clause – It stipulates the rows or tuples which an individual have to alter.

Instruction For Altering The Information In A Table / Relation

An individual must think through the subsequent rules at the time of altering the information in the rows or tuples any tables or relations:

· An alteration can be performed on merely one (1) single table or relation at a single point of time.

· When the alteration disagrees with any reliability constrictions, the complete alteration process is rolled back.

Think through, an instance the subsequent table or relation shows a number of example information form the Customer . Details table or realtion:

CustomerID

Name

Gender

Address

EmailID

Phone

SocialSecurityID

AccountType

C#25001

My Name

Male

My Address Line 1 , Address Line 2

MyEmailID . com

0123456789

SSID#3535359

Loan

C#25051

Our Name

Female

Our Address Line 1 , Address Line 2

OurEmailID . com

0987656789

SSID#9586850

Fixed Deposit

C#25251

Your Name

Female

Your Address Line 1 , Address Line 2

YourEmailID . com

8765434678

SSID#0595759

Savings

C#25341

His Name

Male

His Address Line 1 , Address Line 2

HisEmailID . com

8585856789

SSID#3957859

Loan

Sample Data Values For The Customer . Details Table / Relation

The subsequent command alters the Phone column or attribute of the CustomerIDC#25251:

UPDATE Customer . Details SET Phone = ‘ 8766838450 ’ WHERE CustomerID = ‘ C#25251

The Customer . Details table or relation would look like as follows:

CustomerID

Name

Gender

Address

EmailID

Phone

SocialSecurityID

AccountType

C#25001

My Name

Male

My Address Line 1 , Address Line 2

MyEmailID . com

0123456789

SSID#3535359

Loan

C#25051

Our Name

Female

Our Address Line 1 , Address Line 2

OurEmailID . com

0987656789

SSID#9586850

Fixed Deposit

C#25251

Your Name

Female

Your Address Line 1 , Address Line 2

YourEmailID . com

8766838450

SSID#0595759

Savings

C#25341

His Name

Male

His Address Line 1 , Address Line 2

HisEmailID . com

8585856789

SSID#3957859

Loan

Sample Data Values For The Customer . Details Table / Relation

Think through an additional instance where an individual is required to alter the ApprovalStatus column or attribute of a pending loan of any customer, from Pending to Approved in the LoanPending table or relation. For doing this job, an individual is required to refer to the Customer . Loan table or relation to get the LoanID. Yan individual can modify the particulars by means of the subsequent command:

UPDATE Management . LoanPending SET ApprovalStatus = ‘ Approved ’ FROM LoanPending P , Customer . Loan L WHERE P . LoanID = L. LoaniD AND

P . CustomerID = L . CustomerID

As soon as the previous statement is implemented, the ApprovalStatus will get altered to ‘Approved’.

Removing Information From The Table / Relation

An individual is required to remove the information from the table or relation of a particular database once it is no longer necessary. The minutest part which can be removed from a database is a row or tuple. An individual can remove a row or tuple from a table or relation by means of the DELETE Data Modification Language (DML) command. The code of the DELETE command is as follows:

DELETE [ FROM ] My_Table_Name [ FROM Condition_Tables ] [ WHERE Clause ]

here,

· My_Table_Name – It stipulates the name of the table or relation from which an individual have to remove information.

· Condition_Tables – It stipulates the name of the table(s) or relation which is needed to fix some sort of condition for removal.

· Clause – It stipulates the rows or tuples which an individual have to be remove.

Think through an instance, the subsequent command removes the customer particulars of CustomerID C#25051 from the Customer . Details table or relation:

DELETE Customer . Details WHERE CustomerID = ‘ C#25051 ’

Removing Information From Linked Tables / Relation

At the time of removing the information from the linked tables or relations, an individual is required to guarantee that he or she first remove the information from the table or relation which has the Foreign Key then from the table or relation which has the Primary Key.

Think through an instance of the XYZBank database. The Customer . Details table or relation has the information of the customers who have closed their bank accounts. This information is not necessary to any further extent. This in a consequence upsurges the volume of the database. An individual is needed to confirm that this useless information is deleted form the Customer . Details table or relation. An individual can remove this particular information by means of the subsequent SQL command:

DELETE FROM Customer . Details WHERE AccountStatus = ‘ Closed ’

The database has the tables or relations which are linked with the Customer . Details table or relation. The linked tables or relations are Customer . Loan, Customer . SavingsAc, and the Management . LoanPending. The CustomerID column or attribute in these tables or relations are the Foreign Key to CustomerID column or attribute of the Customer . Details table or relation. Thus, the command ends in an error. So, an individual is required to remove information from the linked tables or relation earlier to implementing the previous DELETE command.

In the upcoming part we will be going through the Removing Every Information From The Table / Relation, Handling The Extensible Markup Language (XML) Information, Stowing Extensible Markup Language (XML) Information In A Table / Relation and its one sub – parts which is Stowing The Extensible Markup Language (XML) Information In A ROWSET along with the different sub – modules of it like Break Down The Extensible Markup Language (XML) File, Obtain A ROWSET From The Tree, Stock The Information Form The ROWSET and Release The Memory in details.