Archive | Databases RSS feed for this section

Update consecutive numbering with UPDATE statement – MSSQL

I want to update a table with consecutive numbering starting at 1. The update has a where clause so only results that meet the clause will be renumbered. Can I accomplish this efficiently without using a temp table?

Read full story Comments { 0 }

What is – DML, DDL, DCL and TCL – Introduction

DML DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT statements DDL DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP statements DCL DCL is […]

Read full story Comments { 0 }

Oracle – Create Table by Select Statement

–WITH data –without data — For example, create a table named EMPLOYEE3 that includes all — of the column definitions from EMPLOYEE where the DEPTNO = D11.

Read full story Comments { 0 }

Find Duplicate Values with SQL Script

Let’s assume that data has been entered in a table and now you want to find if a table column have duplicated values. Here is an SQL script to join data from one table for comparison purpose. The table must have a primary key column (unique column) and the second column for comparison. Here is […]

Read full story Comments { 0 }

List all triggers in SQL Server database with table name and schema

Sample query: Here is and another version where you can the code of the trigger:

Read full story Comments { 0 }

FORMAT Function for Transact-SQL – T-SQL

Returns a value formatted with the specified format and optional culture in SQL Server 2016. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. FORMAT ( value, format [, culture ] ) Return Types:  nvarchar or null The length of the return value is determined by the format. Remarks:   FORMAT returns […]

Read full story Comments { 0 }

MySQL Store Procedure to Loop with Table Cursor and Update with UUID()

This is a sample MySQL store procedure where I loop in all rows in a table and then I update each row with a UUID().

Read full story Comments { 0 }

Oracle PL/SQL – Finding number of rows in each table by a single query

Sample: Results: OR

Read full story Comments { 0 }