Removing Rows with the DELETE Statement in SQL Server #sql #server, #delete #statement, #tsql, #t-sql


#

T-SQL Programming Part 8 – Removing Rows with the DELETE Statement in SQL Server

Greg Larsen explores how to remove rows from a SQL Server table using the DELETE statement.

In my last two articles I discuss using the INSERT and UPDATE statement. These two commands added new rows and modified existing rows. In this article I will explore how to remove rows from a table using the DELETE statement.

Syntax of the DELETE Statement

You may want to delete rows because they are no longer needed, or they were incorrectly added in the first place. The DELETE statement is used to remove rows from a SQL Server table. A single DELETE statement can remove a single row, or number of rows. Here is the basic syntax of the DELETE statement.

( expression ) – is a number or an expression that equates to a number used to limit the number of rows deleted

object – is the name of an object in a database from which you want to delete records

OUTPUT Clause – identifies the column values of the deleted rows to be returned from the DELETE statement

search_condition – the condition used to identify the rows to be deleted

For the complete syntax of the DELETE statement refer to Books Online.

In order to demonstrate how to use the DELETE statement I will be creating a DemoDelete table. Here is the code I used to create and populate my DemoDelete table.

Deleting a Single Row Using WHERE Constraint

In order to delete a single row from a table you need to identify that row with a WHERE constraint. Below is some code that deletes a single row from my DemoDelete table:

In this code I used the DeleteDesc column to constrain the records that I would be deleting. By specifying that the DeleteDesc column value had to be equal to the value “The Mother”, only one record in my table got deleted, because only one row in my table had that value. Now if my table contained a number of rows that had a column value of “The Mother” then all the rows that contained that value would be deleted.

If you are unsure of the rows you are identifying to be deleted using the above example, and you want to make sure the rows you have targeted with the WHERE constraint are correct, then you can first run a SELECT statement. After you are confident that your SELECT statement is selecting the rows you want to delete you can then convert it to a DELETE statement.

Using the TOP Clause to Delete a Single Row

You can also use the TOP clause to delete a single row. Below is an example where I used the TOP clause to delete one row from my DemoDelete table:

This statement deleted a random row from my DemoDelete table. It was random because SQL Server does not guarantee a sorted set will be returned where it can delete the top record of the ordered set. When I review the records left in my table I see I deleted the record that had an Id value of 1 and a DeleteDesc of “Thing One”. Note if I change the TOP clause to another number like 3, then this statement would delete the number of rows equal to the value specified.

Deleting the TOP 1 Records from a Sorted Set

If you want to delete the first record from a sorted set you need to write your TSQL DELETE statement similar to the following code:

In the above code I create a subquery that returned a single ID value based on the descending sort order of ID column value in my DemoDelete table. I then used the WHERE constraint to only delete records that had that ID value. I also place a TOP (1) clause on my DELETE statement to only delete a single row should my DemoDelete table contain multiple records with the same ID value. If you are following along you can see the above code deleted the DemoDelete record that had an ID value of 7.

Since my DemoDelete table did not contain multiple records with the same ID value I could have also deleted the largest ID value row by running the following code:

When I run this code against my DemoDelete table it will delete ID value of 5.

Using Another Table to Identify the Rows to Delete and the OUTPUT Clause

There are times when you might what to delete the rows in a table based on values from another table. An example of where you might want to do this is to remove rows from your inventory table based on some sales data. To demo this first I will need to generate another table that contains key values for the rows I want to delete. Here is the code to create and populate my other table:

At this point after running all my different DELETE statements against my DemoDelete table there are only three rows left in my table. By selecting all the rows in my DemoDelete table I see that these three rows are left:

In order to use the RecordsToDelete table to delete specific records in my DemoDelete table I need to run the code below.

This code joins the table DemoDelete and RecordsToDelete based on the DeleteDesc column. When the DeleteDesc matches between the two tables the matched rows within the DemoDelete table are deleted.

My delete statement above also contains the OUTPUT clause. The OUTPUT clause is used to return the column values of the deleted rows that are identified in the OUTPUT clause. In the code above I specified “DELETED.*”. The “*” means to return all the columns values from the DELETED rows. When I ran this code the following rows were returned:

These returned rows could be used by your application for some purpose, like creating an audit trail.

Inserting OUTPUT Clause Data into a Table

There are times when you might retain the data created by the OUTPUT clause in a table instead of just returning the deleted row values to the application. To demonstrate running a DELETE statement that populates the row values being deleted into a table I will run the code below.

The following output displayed the SELECT statement in the above code snippet:

In both of my examples that used the OUTPUT clause of the DELETE statement I specified “DELETED.*” to denote outputting all the column values for the rows being deleted. I could have specified the actual column values I wanted to output. The code below is equivalent to the code above.

In this code you can see I specified “DELETED.ID, DELETED.DeleteDesc”, instead of “DELETE.*”. You can verify this code is equivalent by inserting the “The Cat” row back into the DemoDelete table and then running the code above.

Multiple Ways to Delete Rows

As you can see there are multiple ways to delete rows from a SQL Server table. You can use the WHERE clause to identify specific criteria for the rows that need to be deleted. You can join a table to the table in which you are deleting rows to identify which rows to delete. You can even use the TOP clause to restrict the number of rows that will be deleted. The article should help you with developing your DELETE statement next time you have to remove some rows from a SQL Server table.


PHP PDO – exec (INSERT, UPDATE, DELETE) MySQL #exec, #pdo #exec, #insert, #update, #delete


#

PHP PDO – exec (INSERT, UPDATE, DELETE) MySQL

PHP-MySQL

After the connection to database is successfully created and the PDO object instance is set, the object can be used to perform SQL queries.
The SQL queries with PDO can be made in two ways:
– directly using ” exec() “, and ” query() ” methods,
– or with the prepare(). execute() statement.
The first variant is more simple, in this lesson it’s presented the exec method.

The queries that modify rows in the table, but not return a result set with rows and columns (INSERT, UPDATE, and DELETE), are send with exec(). this method returns the number of affected rows, or FALSE on error.

$count = $conn- exec(“SQL Query”);

Queries that select rows (SELECT) and return a result set with rows and columns are sent with the query() method. In case of error, returns FALSE.

$res = $conn- query(“SQL Query”);

To work with databases in PHP, you must know the specific SQL queries as: CREATE TABLE, INSERT, SELECT, UPDATE, etc.
These queries are send as a string to the MySQL server.

Create MySQL table

To create a table in a MySQL database, use the ” CREATE TABLE `table_name` ” query, and the exec() method:

– All these instructions are added after the PDO object is created, containing the connection to MySQL database.

In the next example it is created in a database named “tests” a table named “sites”, with 4 colummns: “id”, “name”, “category”, and “link”. – If the table is created, the code above will display:

The sites table is created

– The instruction: ” exec(“SET CHARACTER SET utf8″) ” sets the transfer of data between PHP and MySQL to be made with UTF-8 encoding. It is advisable to add this instruction especialy when working with data containing diacritics or special characters, but the PHP script should also contain this header: header(‘Content-type: text/html; charset=utf-8’); .

INSERT

Once the MySQL table is created, you can add rows with data. To add data into a table, use an INSERT command, in the exec() method.
Sintax:

$objPDO- exec(“INSERT INTO `table_name` (`column1`, `column2`. ) VALUES (‘value1’, ‘value2’. )”);

Example: – This code adds 3 rows in the “sites” table. The $count variable stores the number of affected rows (added).
This script will display:

Number of rows added: 3

The “sites” table will contain these data:
To get the last auto-inserted “id” (in a AUTO_INCREMENT PRIMARY KEY column), use the lastInsertId() method.
– When you add multiple rows in the same INSERT query, this method will return the ID of the first added row.

UPDATE. and DELETE are SQL instructions that changes data in a table, but not return a result set with rows and columns. They can be executed in the same way as INSERT, with the exec() method.

UPDATE

The data in the rows of a MySQL table can be modified with the SQL command INSERT .
Syntax:

$objPDO- exec(“UPDATE `table_name` SET `column1`=’value1′, `column2`=’value2′ WHERE condition “);

The next example changes data in the columns “name” and “link”, where “id” is 3; in the “sites” table (created with the code above). – Result:

Affected rows. 1

Sometimes an UPDATE query not affect any row (if the condition not matches), and will return 0. So, it is indicated to use this statement to check the result: if($count !== false) .
– Not: if(!$count)

DELETE

The DELETE instruction deletes rows in a table.
Syntax:

The next example deletes all the rows in the “sites” table where the value in “category” column is ” education ” or ” programming “. – Result:

AAffected rows: 2

– In the next lesson you can learn how to select and get data stored in a MySQL table, using the PDO query() method.

M E N U


How to Add a Digital Signature into PDF Document #software, #function, #pdf, #document, #pdf #file, #pdf #document, #adobe, #acrobat #reader, #adobe #acrobat, #document #management, #new, #blank, #page, #merge, #merger, #combine, #insert, #split, #splitter, #extract, #split, #reorder, #renumber, #extract, #replace, #delete, #rotate, #rotation, #crop, #margin, #action, #bookmark, #xml, #import, #export, #header, #footer, #watermark, #stamp, #stamper, #style, #text, #image, #bmp, #jpg, #gif, #png, #tiff, #attach, #attachment, #javascript, #js, #digital #signature


#

PDF Document Management 14: Add a Digital Signature into PDF Document

A digital signature (ID). like a conventional handwritten signature, identifies the person signing a document. Unlike a handwritten signature, a digital signature is difficult to forge because it contains encrypted information that is unique to the signer and easily verified. It is usually password protected and can be stored on your computer in PKCS #12 file format (Personal Information Exchange File, PFX).

When you buy a new digital signature, you get .spc file that contains the certificate and .pvk file that contains your private keys. U se Microsoft command-line tool Pvk2Pfx to convert them into a PFX file. See detail here.

The following command generates the .pfx file Mypfxfile.pfx from Mypvkfile.pvk and Myspcfile.spc. The command supplies the password mypassword for the .pvk file, which becomes the password for the .pfx file Mypfxfile.pfx:

pvk2pfx -pvk mypvkfile.pvk -pi mypassword -spc myspcfile.spc -pfx mypfxfile.pfx

This function can create document-level Digital Signature that apply to the entire document.

Here are the steps on how to Add Digital Signature into PDF Document.

2. Select a PFX file with digital signature.

3. The Create Digital Signature to Document dialog box comes up:

  • Select a File. Select a PFX file with digital signature.
  • Enter Password. The password for the digital ID.
  • Allow Multiple Signatures. If checked, the signature and all the other content will be added as a new revision thus not invalidating existing signatures.
  • Keystore Type. The specified keystore type. pkcs12 is the most common type.
  • Keystore Provider. Sets the Cryptographic Service Provider that will sign the document. Leave it empty for pkcs12 type.
  • Signed Type. The mode can be Self signed (Adobe.PPKLite). VeriSign plug-in (VeriSign.PPKVS) and Windows Certificate Security (Adobe.PPKMS).
  • Certification Level. The certification level can be NOT_CERTIFIED. CERTIFIED_NO_CHANGES_ALLOWED. CERTIFIED_FORM_FILLING and CERTIFIED_FORM_FILLING_AND_ANNOTATIONS .
  • Reason. Sets the signing reason.
  • Location. Sets the signing location.
  • Contact. Sets the signing contact.
  • Appearance.
    • Visible. Sets the signature to be visible on the Position of the selected Page.
    • Page Number. Select the page number for the digital signature.
    • Appearance: There are 4 Options: Description Only, Signer Name and Description, Signature Image and Description, and Signature Image only.
    • Let/Right/Top/Bottom. The position for the digital signature.
    • Use Signature Image. Set the signature image if the Appearance option is Signature Image and Description
    • Use Background Image. Set the background image. Leave it blank if no background image.
    • Background Image Scale. Set the scaling to be applied to the background image.

4. Click Close to finish.

Question 1: The Validity of digital signature is displayed as UNKNOWN inside Adobe Reader, how to make Signature Valid ?

Answer:
1. Click inside the signature with Validity Unknown ;
2. The dialog of Signature Validity Status will show up. Click Signature Properties ;
3. From Dialog Signature Properties . click Show Signer’s Certificate ;
4. From Dialog Certificate Viewer , click Trust Tab and then click Add to Trusted Certificates ;
5. Import Contact Settings will come up, click OK.
6. Close Adobe Reader and open your PDF again. You will see the Signature Valid .

Question 2: How to remove the imported digital signature from my computer?

Answer:
1. Open Adobe Reader= Edit Menu= Preferences= Signatures= Identities Trusted Certificates= More;
2. Dialog Digital ID and Trusted Certificate Settings comes up, click Trusted Certificates . Select the Certificate to remove.
3. From Internet Explorer = Tools Menu = Internet Options= Contents= Certificates= Select the Certificate to remove.

PDFill Copyright � 2002-2017 by PlotSoft L.L.C. All rights reserved.


Net2ftp – a web based FTP client #net2ftp, #web, #ftp, #based, #web-based, #xftp, #client, #php, #ssl, #password, #server, #free, #gnu, #gpl, #gnu/gpl, #net, #net #to #ftp, #netftp, #connect, #user, #gui, #interface, #web2ftp, #edit, #editor, #online, #code, #php, #upload, #download, #copy, #move, #delete, #zip, #tar, #unzip, #untar, #recursive, #rename, #chmod, #syntax, #highlighting, #host, #hosting, #isp, #webserver, #plan, #bandwidth


#

Once you are logged in, you will be able to:

Navigate the FTP server

Once you have logged in, you can browse from directory to directory and see all the subdirectories and files.

There are 3 different ways to upload files: the standard upload form, the upload-and-unzip functionality, and the Java Applet.

Navigate the FTP server

Once you have logged in, you can browse from directory to directory and see all the subdirectories and files.

Click on a filename to quickly download one file.
Select multiple files and click on Download; the selected files will be downloaded in a zip archive.

and save the zip archive on the FTP server, or email it to someone.

Different formats are supported. zip. tar. tgz and .gz.

Choose from a list of popular applications (PHP required).

Copy, move and delete

Directories are handled recursively, meaning that their content (subdirectories and files) will also be copied, moved or deleted.

Copy or move to a 2nd FTP server

Handy to import files to your FTP server, or to export files from your FTP server to another FTP server.

Rename and chmod

Chmod handles directories recursively.

View code with syntax highlighting

PHP functions are linked to the documentation on php.net.

Plain text editor

Edit text right from your browser; every time you save the changes the new file is transferred to the FTP server.

Edit HTML a What-You-See-Is-What-You-Get (WYSIWYG) form; there are 2 different editors to choose from.

Edit HTML and PHP in an editor with syntax highlighting.

Search for words or phrases

Filter out files based on the filename, last modification time and filesize.

Calculate the size of directories and files.