Data Update

Definition

Thedatatableisanimportantobjectofthedatabaseandthebasicunitforstoringdata.Afterthetablestructureiscreated,itinvolvesinsertingnewdataintothetable,aswellasmodifyinganddeletingexistingdata,whichisdataupdate.Dataupdatecanbeimplementedintwoways:"ObjectExplorer"andT-SQLstatements.ThedataupdatefunctionoftheSQLlanguageensuresthattheDBAordatabaseusercanmaintaindataontheestablisheddatabase.

Datarevisionisaprocessofnewdataitemsorrecords,andreplacementofolddataitemsorrecordsinadatafileordatabase.Thisisachievedbydeleting,modifyingandinsertingoperations.Itisthetimeseriesofgeographicinformationsystemthatestablishesgeographicdatatomeetthepremiseofdynamicanalysis.Remotesensingdataisanimportantsourceandbasisforthematicdataupdateofgeographicinformationsystemduetoitsmulti-temporalandfastacquisitioncharacteristics.Theconditionforrealizingthisprocessistoeffectivelyimprovethegeometricaccuracyandclassificationaccuracyofremotesensingimagedata.Atthesametime,itisnecessarytoestablishasysteminterfacebetweentheremotesensingdataprocessingsystemandthegeographicinformationsystemtoimprovetheaccuracyandefficiencyofdifferentdatastructuresanddataconversion.

Dataupdateincludesthreetypesofoperations:insert,modify,anddeletedata.

Datainsertion

SQLdatainsertiongenerallyhastwoformats,oneissingle-rowdatainsertion,andtheotherissubqueryresultinsertion.ThekeywordisINSERT.

Singlerowdatainsertion

1.Thegeneralformofsinglerowdatainsertionis:

INSERTINTOtablename(columnname1,columnname2,...,columnNamen)

VALUES(constant1,constant2,…,constantn)

where:thetablenameisthetableofcontentstobeinsertedintothedata;inthenewrecord,thecolumnnameis1Thevalueofisconstant1,thevalueofcolumnname2isconstant2,...,thevalueofcolumnnamenisconstantn.TheattributelistofthecatalogtablethatdoesnotappearintheINTOclausewilldefaulttoanullvalueinthenewdata.

Note:Whentheconstantstructure(includingorderanddatatype)intheVALUESclauseisthesameasthestructureofthetableofcontents,thecolumnnamesintheINTOclausecanbeomitted.

2.Examples

Example1:Recordanewcourse(coursenumber:03-01;coursename:operatingsystem;hours:48;credits:3;semester:4;TeacherID:X401055;Classroom:20303)insertedintothecurriculum.

INSERTINTOcurriculum

VALUES('03-01','operatingsystem',48,3,'4','X401055','20303')

YoucanomitthecolumnnamesintheINTOclauseatthistime.

Example2:Insertanewgraderecord(studentnumber:040101;coursecode:03-01)intothegradetable.

INSERTINTOtranscript(studentnumber,coursecode)

VALUES('040101','03-01')

ItisnotpossibletoomitINTOatthistimeThecolumnnameintheclause.

Insertsub-queryresults

1.Datainsertioncanalsoinsertsub-queryresultsandmultipledatarecordsintothetargetrecordinbatches.Thegeneralformatisasfollows:

INSERTINTOtablename(columnname1,columnname2,...,columnnamen)

Subquery

2.Examples

Example:Findtheaveragegradeforeachcourse,andputtheresultintothetargettable,thatis,PJ(coursenumber,averagegrade).

INSERTINTOPJ(coursenumber,averagegrade)

SELECTcoursenumber,AVG(grade)

FROMscoretable

GROUPBYcoursenumber

Datamodification

Generalformat

ThekeywordfordatamodificationisUPDATE,thegeneralformatis:

UPDATEtablename

SETupdatecontent

WHEREupdatecondition

Amongthem,theupdatecontentintheSETclauseappearsintheformof"attributename=expression".

Example

Example1:ChangethenativeplaceofLiKuitoShandong.

UPDATEStudentForm

SETHometown='Shandong'

WHEREName='LiKui'

Example2:ChangefromLiaoningProvinceAllstudentscoresaresetto80

UPDATEscoretable

SETscore=80

WHEREstudentIDIN(SELECTstudentID

FROMStudenttable

WHEREnativeplace='Liaoning')

Note:Subqueriescanbenestedintheupdateoperationtocompletetheupdateofcomplexlogic.

Datadeletion

Generalformat

ThekeywordfordatadeletionisDELETE,thegeneralformatis:

DELETE

FROMtablename

WHEREdeletecondition

Amongthem,theDELETEclausedeletesthedatainthetableanddoesnotaffectthestructureofthetable.

Example

Example:DeletethescorerecordofthestudentwhosestudentIDis‘040104’.

DELETE

FROM

cWHEREstudentID='040104'

Note:IftheWHEREclauseisnotadded,thetargettablewillbedeletedAllrecordsin.

Related Articles
TOP