SQL Tutorial: Select, Update & Delete Syntax

Leave a comment Standard

Select Statements

General Syntax

  • SELECT [column], [column], [column] FROM [tablename] WHERE [column] = [value]

Select All Columns

  • SELECT * FROM [tablename]

Limit # of Records Returned – MSSQL

  • SELECT TOP [number] * FROM [tablename]

Limit # of Records Returned – SQL

  • SELECT * FROM [tablename] LIMIT [# from], [# to]
    • this one works well for pagination. You can select results 0 – 30, then 31 to 60, etc.
  • SELECT * FROM [tablename] LIMIT [number]
    • use this one if you just want to cut off the number of records returned

Sum A Field By Status ID

  • SELECT sum([column]), status_id FROM [tablename] WHERE [column] = [value] GROUP BY status_id

Count A Field

  • SELECT count([column]) FROM [tablename] WHERE [column] = [value]

Sort By A Field Ascending or Descending

  • SELECT [column], [column] FROM [tablename] WHERE [column] = [value] ORDER BY [column] DESC, [column] ASC

Complex Where Values

  • SELECT [column], [column] FROM [tablename] WHERE ([column] = [value] AND [column] = [value]) OR ([column] = [value] AND [column] = [value])

Select From Result Subset

  • SELECT [column], [column] FROM [tablename] WHERE [column] IN ([value], [value], [value])

Select From Select Subset

  • SELECT [column] FROM [tablename] WHERE [column] IN (SELECT [column] FROM [tablename])

Inner Join Select All – records must exist in table B and table A matching on conditions

  • SELECT * FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value]

Left Join Select All – records must exist in table A

  • SELECT * FROM [tablename] A LEFT OUTER JOIN [tablename] B on A.[column] = B.[column] WHERE B.[column] = [value]

Inner Join Select All From One Table, Select Fields From Another

  • SELECT A.*, B.[column], B.[column] FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value]

Unions – results and columns must match in both SQL statements

  • SELECT A.*, B.[column], B.[column] FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value] UNION SELECT C.*, D.[column], D.[column] FROM [tablename] C INNER JOIN [tablename] D ON C.[column] = D.column WHERE C.[column] = [value]

Delete Statements

Always do a select statement first to make sure the records you’re about to delete are the ones you want. Also consider any triggers that may be fired as a result of deleting these records.

General Syntax

  • DELETE FROM [tablename] WHERE [column] = [value]

Check Yourself

  • SELECT * FROM [tablename] WHERE [column] = [value]
  • Now replace the text in red with the text in blue below to delete the records you found
  • DELETE FROM [tablename] WHERE [column] = [value]

Update Statements

Make sure you do a select statement before attempting to update records to ensure you only update the records you intended. Also consider any triggers that may be fired as a result of updating these records.

General Syntax

  • UPDATE [tablename] SET [column]=[value], [column]=[value], [column]=[value] WHERE [column]=[value]

Check Yourself

  • SELECT * FROM [tablename] WHERE [column] = [value]
  • Replace the blue section with what’s in red below, then add the columns and values you want to update as seen in green below
  • UPDATE [tablename] SET [column]=[value], [column]=[value], [column]=[value] WHERE [column]=[value]
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s