UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task. Let us see the following example. We have two tables Table 1…

Read More

MySQL TRIM() function

MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string. Syntax TRIM( str) Arguments Name Description BOTH Indicates that prefixes from both left and right are to be removed. LEADING Indicates that only leading prefixes are to be removed. TRAILING Indicates that only trailing prefixes are to be removed. remstr The string to be removed. FROM Keyword str The actual string from where remstr is to…

Read More

Create an ODBC Connection to Access Database

The Microsoft Access, also known as MSAccess, is a management system database from Microsoft, packaged with Microsoft Office Professional which combines the Microsoft Database Engine with a graphical user interface. In Windows 64bit to check whether the 32-bit Microsoft Access Database Engine drivers are installed, go to C:\Windows\SysWOW64\odbcad32.exe. If on a 32-bit machine, go to C:\Windows\System32\odbcad32.exe to access the utility.  Go to the drivers tab. If the Microsoft Access Driver lists .mdb and .accdb, then the correct drivers are installed….

Read More

SQL Joins

SQL joins are used to combine rows from two or more tables. SQL JOIN An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met. Let’s look at a selection from the “Orders” table: OrderID CustomerID OrderDate 10308 2 1996-09-18 10309 37…

Read More

Querying the tables using JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The…

Read More

Posted in Reference, SQL Tagged Comments Off on Querying the tables using JOIN Keyword
Who is Active for MSSQL

Who is Active is free SQL tool that you can download and use for personal, educational, and internal corporate purposes. If you work with MSSQL server and notice slowness this is a great tool for monitoring SQL transactions and performance in SQL server.  I personally use this tool for monitoring SQL deadlocks in the database. You can find more information in this page: http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx You can download the SQL script from here: http://sqlblog.com/files/folders/beta/entry42453.aspx The installation process is very simple:…

Read More

SQL FORMAT() Function

The FORMAT() Function The FORMAT() function is used to format how a field is to be displayed. SQL FORMAT() Syntax SELECT FORMAT(column_name,format) FROM table_name;   Parameter Description column_name Required. The field to be formatted. format Required. Specifies the format. Example SELECT ProductName, Price, FORMAT(Now(),’YYYY-MM-DD’) AS PerDate FROM Products;  

Read More

Posted in SQL Tagged Comments Off on SQL FORMAT() Function
SQL NOW() Function

The NOW() Function The NOW() function returns the current system date and time. SQL NOW() Syntax SELECT NOW() FROM table_name; Example SELECT ProductName, Price, Now() AS PerDate FROM Products;

Read More

Posted in SQL Tagged Comments Off on SQL NOW() Function
SQL ROUND() Function

The ROUND() Function The ROUND() function is used to round a numeric field to the number of decimals specified. SQL ROUND() Syntax SELECT ROUND(column_name,decimals) FROM table_name;   Parameter Description column_name Required. The field to round. decimals Required. Specifies the number of decimals to be returned. Example SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

Read More

Posted in SQL Tagged Comments Off on SQL ROUND() Function
SQL LEN() Function

The LEN() Function The LEN() function returns the length of the value in a text field. SQL LEN() Syntax SELECT LEN(column_name) FROM table_name; Example SELECT CustomerName,LEN(Address) as LengthOfAddress FROM Customers;

Read More

Posted in SQL Tagged Comments Off on SQL LEN() Function