Set custom messages by working with MySQL IF Statements and SELECT in a user-defined variable

MySQLMySQLi Database

<p>Let us first create a table &minus;</p><pre class="result notranslate">mysql&gt; create table DemoTable2008 ( &nbsp; &nbsp;Value int ); Query OK, 0 rows affected (10.59 sec)</pre><p>Insert some records in the table using insert command &minus;</p><pre class="result notranslate">mysql&gt; insert into DemoTable2008 values(10); Query OK, 1 row affected (0.20 sec) mysql&gt; insert into DemoTable2008 values(20); Query OK, 1 row affected (0.08 sec) mysql&gt; insert into DemoTable2008 values(30); Query OK, 1 row affected (0.15 sec) mysql&gt; insert into DemoTable2008 values(-31); Query OK, 1 row affected (0.16 sec) mysql&gt; insert into DemoTable2008 values(-28); Query OK, 1 row affected (0.17 sec)</pre><p>Display all records from the table using select statement &minus;</p><pre class="prettyprint notranslate">mysql&gt; select * from DemoTable2008;</pre><p>This will produce the following output &minus;</p><pre class="result notranslate">+-------+ | Value | +-------+ | &nbsp; &nbsp;10 | | &nbsp; &nbsp;20 | | &nbsp; &nbsp;30 | | &nbsp; -31 | | &nbsp; -28 | +-------+ 5 rows in set (0.00 sec)</pre><p>Here is the query to first work with a user-defined variable and taking input as a SELECT &minus;</p><pre class="prettyprint notranslate">mysql&gt; set @value:=(select sum(Value) from DemoTable2008); Query OK, 0 rows affected (0.03 sec) mysql&gt; select if(@value &gt; 0, &#39;Value is greater than 0&#39;,&#39;Not greater than&#39;) as Result;</pre><p>This will produce the following output &minus;</p><pre class="result notranslate">+-------------------------+ | Result &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| +-------------------------+ | Value is greater than 0 | +-------------------------+ 1 row in set (0.00 sec)</pre>
raja
Updated on 02-Jan-2020 06:04:30

Advertisements