Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Chapter 28 SQL Command DELETE (2)

The sample

The following two examples remove all rows from the TempEmployees table. Note that the FROM keyword is optional:

DELETE FROM TempEmployees
Copy the code

DELETE TempEmployees
Copy the code

The following example removes employee number 234 from the Employees table:

DELETE
     FROM Employees
     WHERE EmpId = 234
Copy the code

The following example removes all rows from the ActiveEmployees table where the CurStatus column is set to “RETIRED” :

DELETE FROM ActiveEmployees
     WHERE CurStatus = 'Retired'
Copy the code

The following example deletes rows using a subquery:

DELETE FROM (SELECT Name,Age FROM Sample.Person WHERE Age > 65)
Copy the code

Embedded SQL and dynamic SQL examples

In the following set of program examples, the first program creates a table named SQLUser.wordpair with three columns. The next program inserts six records. Subsequent programs use cursor-based embedded SQL to delete all English records and dynamic SQL to delete all French records. The last program displays the remaining records and then deletes the table.

ClassMethod Delete1(a)
{
	&sql(
		CREATE TABLE SQLUser.WordPairs 
		(
			Lang        CHAR(2) NOT NULL,
			Firstword   CHAR(30),
			Lastword    CHAR(30)))if SQLCODE = 0{ w ! ."Table created" 
	} elseif SQLCODE = -201{ w ! .Table already exists  
		q
	} else{ w ! ."CREATE TABLE failed. SQLCODE=",SQLCODE 
	}
}
Copy the code

ClassMethod Delete2(a)
{
	#SQLCompile Path = Cinema,Sample
	&sql(
		INSERT INTO WordPairs 
		( Lang, Firstword, Lastword ) 
		VALUES 
		(
			'En'.'hello'.'goodbye'
		)
	)
	if SQLCODE = 0{ w ! ."First record inserted" 
	} else{ w ! ."Insert failed, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('Fr'.'bonjour'.'au revoir'))
	if SQLCODE = 0{ w ! ."Second record inserted" 
	} else{ w ! ."Insert failed, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('It'.'pronto'.'ciao'))
	if SQLCODE = 0{ w ! ."Third record inserted" 
	} else{ w ! ."Insert failed, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('Fr'.'oui'.'non'))
	if SQLCODE = 0{ w ! ."Fourth record inserted." 
	} else{ w ! ."Insert failed, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('En'.'howdy'.'see ya'))
	if SQLCODE = 0{ w ! ."Insert fifth record" 
	} else{ w ! ."Insert failed, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('Es'.'hola'.'adios'))
	IF SQLCODE = 0{ w ! ."Insert sixth record.",!!!!! s myquery ="SELECT %ID,* FROM SQLUser.WordPairs"
		s tStatement = ##class(%SQL.Statement%).New(a)s qStatus = tStatement.%Prepare(myquery)
		if qStatus'=1 { w "%Prepare failed:" d $System.Status.DisplayError(qStatus) q } s rset = tStatement.%Execute() d rset.%Display() w ! ,"End of data" } else { w ! ,"Insert failed,SQLCODE =",SQLCODE q}}Copy the code

ClassMethod Delete3(a)
{

	#SQLCompile Path=Sample
	n %ROWCOUNT,%ROWID
	&sql(
		DECLARE WPCursor CURSOR FOR 
		SELECT Lang FROM WordPairs
		WHERE Lang='En'
	)
	&sql(
		OPEN WPCursor
	)
	q:(SQLCODE'=0) for { &sql( FETCH WPCursor ) q:SQLCODE &sql( DELETE FROM WordPairs WHERE CURRENT OF WPCursor ) if SQLCODE=0 { w ! ,"Delete succeeded "w! ,"Row count=",%ROWCOUNT," RowID=",%ROWID } else { w ! ,"Delete failed,SQLCODE =",SQLCODE} & SQL (CLOSE WPCursor)}Copy the code

ClassMethod Delete4(a)
{
	s sqltext = "DELETE FROM WordPairs WHERE Lang=?"
	s tStatement = ##class(%SQL.Statement%).New(0,"Sample")
	s qStatus = tStatement.%Prepare(sqltext)
	if qStatus'=1 { w "%Prepare failed:" d $System.Status.DisplayError(qStatus) q } s rtn = tStatement.%Execute("Fr") if rtn.%SQLCODE=0 { w ! ,"Delete succeeded" w ! ,"Row count=",rtn.%ROWCOUNT," RowID of last record=",rtn.%ROWID } else { w ! ,"Delete failed, SQLCODE=",rtn.%SQLCODE } }Copy the code

ClassMethod Delete5(a)
{

	s myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
	s tStatement = ##class(%SQL.Statement%).New(a)s qStatus = tStatement.%Prepare(myquery)
	if qStatus'= 1 {w "% Prepare failure:" d $System. The Status. The DisplayError (qStatus) q} s rset = tStatement. % the Execute () d rset. % Display () w! ,"End of data" &sql( DROP TABLE SQLUser.WordPairs ) if SQLCODE=0 { w !! Q} else {w! SQLCODE=",SQLCODE}}Copy the code