Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 215 of 547
MySQL Error ERROR 1099 (HY000): Table was locked with a READ lock and can't be updated
To get rid of LOCK TABLES query, you need to use UNLOCK TABLES.Let us create a table −mysql> create table demo6 −> ( −> country_name varchar(100 −> ) −> ); Query OK, 0 rows affected (1.51 sec)Insert some records into the table with the help of insert command −mysql> insert into demo6 values('US'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo6 values('UK'); Query OK, 1 row affected (0.08 sec) mysql> insert into demo6 values('AUS'); Query OK, 1 row affected (0.11 sec)Display records from the table using select statement −mysql> select *from demo6;This will produce the ...
Read MoreLimit total number of results across tables in MySQL?
For this, you can use UNION ALL along with LIMIT concept. For our example, we will create three tables.Let us create the first table −mysql> create table demo3 −> ( −> value int −> ); Query OK, 0 rows affected (1.39 sec)Insert some records into the table with the help of insert command −mysql> insert into demo3 values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into demo3 values(20); Query OK, 1 row affected (0.08 sec) mysql> insert into demo3 values(30); Query OK, 1 row affected (0.08 sec)Display records from the table using select statement −mysql> select ...
Read MoreMultiple LIKE Operators with ORDER BY in MySQL?
Following is the syntax implementing multiple LIKE operators with ORDER BY −select *from yourTableName order by ( yourColumnName like '%yourValue1%' ) + ( yourColumnName like '%yourValue2%' ) + . . N desc;Let us create a table −mysql> create table demo2 −> ( −> id int not null auto_increment, −> name varchar(100), −> primary key(id) −> ); Query OK, 0 rows affected (1.53 sec)Insert some records into the table with the help of insert command −mysql> insert into demo2(name) values('John'); Query OK, 1 row affected (0.18 sec) mysql> insert into demo2(name) values('David'); Query OK, 1 row affected (0.09 ...
Read MoreHow to get number located at 2 places before decimal point MySQL?
In order to get number located at 2 places before decimal point, you can use the concept of div.Let us create a table −mysql> create table demo1 −> ( −> value float −> ); Query OK, 0 rows affected (2.20 sec)Insert some records into the table with the help of insert command −mysql> insert into demo1 values(456.54); Query OK, 1 row affected (0.16 sec) mysql> insert into demo1 values(50.64); Query OK, 1 row affected (0.17 sec) mysql> insert into demo1 values(1000.78); Query OK, 1 row affected (0.13 sec)Display records from the table using select statement −mysql> select *from demo1;This will ...
Read MoreWhat is the usage of scrollable cursor for absolute positioning?
The SCROLLABLE CURSOR can be used to directly point the cursor position to the mentioned absolute position. The absolute position is the position of a particular row in the result table from the first row.We can fetch the absolute position by using ABSOLUTE parameter in the FETCH statement. For example, we have to declare a scrollable cursor as below.EXEC SQL DECLARE ORDER_CURR SCROLL CURSOR FOR SELECT ORDER_ID, ORDER_DATE FROM ORDERS WHERE ORDER_DATE = ‘2020-07-29’ END-SQLNow if we want to fetch the absolute 9th row then we will ...
Read MoreWhat is the difference between SENSITIVE and INSENSITIVE scrollable CURSOR with syntax
The INSENSITIVE SCROLLABLE CURSOR are sort of read only cursors in which the result table cannot change once the cursor is opened. The other applications also cannot update the INSENSITIVE SCROLLABLE CURSOR once it is opened. The SENSITIVE SCROLLABLE CURSOR, unlike INSENSITIVE are sensitive to changes made in the result table. The changes made by other applications will be reflected in the result table.We can declare SENSITIVE and INSENSITIVE SCROLLABLE CURSOR like below.EXEC SQL DECLARE ORDER_CURR SENSITIVE SCROLL CURSOR FOR SELECT ORDER_ID, ORDER_DATE FROM ORDERS WHERE ORDER_DATE ...
Read MoreWrite the syntax to declare a scrollable cursor on the ORDERS DB2 table.
A SCROLLABLE CURSOR can move in both forward and backward direction. In other words, it can fetch next as well as previous rows. A SCROLLABLE CURSOR is declared using the “SCROLL” clause in the DECLARE CURSOR.For example, if we want to declare a SCROLLABLE CURSOR on the ORDERS table then we have to declare the cursor like below.EXEC SQL DECLARE ORDER_CURR SCROLL CURSOR FOR SELECT ORDER_ID, ORDER_DATE FROM ORDERS WHERE ORDER_DATE = ‘2020-07-29’ END-SQL
Read MoreWhat is the purpose and usage of SCROLLABLE CURSOR in COBOLDB2 program?
A cursor can move only in forward direction, which means that it can extract the next row after every fetch. It is not possible to extract the previous row using a cursor.For example, if our resultant cursor contains following rows−ORDER_IDORDER_DATEA223672020-07-28A667562020-07-28A778902020-07-29A968322020-07-29If our cursor is currently pointing to 3rd row i.e. order id A77890 then the next fetch will point the cursor to the next row i.e. order id A96832. It is not possible to point the cursor to the previous order id i.e. A66756.In order to achieve this, we use the concept of SCROLLABLE CURSOR. The SCROLLABLE CURSOR can move both ...
Read MoreWhat is the purpose and usage of ATOMIC and NON-ATOMIC clause in multi row insert?
The ATOMIC and NON ATOMIC clauses are used with the multi-row insert. ATOMIC is always processed by default if any of the options is not given. The ATOMIC clause states that if there is a failure while inserting any one row during multi-row insertion then the entire query will be failed and all the inserts will be rolled back.The NON ATOMIC clause is just the opposite of the ATOMIC clause. It is used when we have to insert and process all the rows individually in a multi-row insert. For example, this option can be used like below.MOVE 50 TO MAX-ROWS ...
Read MoreHow to insert multiple rows in a table using a single INSERT command in program?
If we want to insert a multiple rows in a DB2 table using a single INSERT command then we have to define the host variable array and move the row data we want to insert in that array. We need to define another variable in the working storage section with configuration S9(4) COMP which will hold the number of rows to be inserted. We can insert the multiple rows as below−MOVE 50 TO MAX-ROWS EXEC SQL INSERT INTO ORDERS (ORDER_ID, ORDER_DATE) VALUES(:ORDER-ID, :ORDER-DATE) FOR :MAX-ROWS ROWS END-EXECThe ORDER-ID and ORDER-DATE are host variables which should be ...
Read More