CODEWITHSUNDEEP
javamysqlsql
Vijay Manohar
Vijay Manohar

Reputation: 493

Using If then else in Mysql

Hi i am a newbie to use MySql statements.

This is my Parent table 

Name   Sell_amt   Mrp_amt    Cost_amt   Goods_Name           Goods_Weight   Goods_quantity
John       100         90         80         Choclate            1lb               5
samson     100         110        60         mobile              0.5lb             8
James      100         100        70         chair               1lb               5

This is my Child table columns 

 
Name   Sell_amt   Mrp_amt    Special_DealStatus

Now i need to update the child table based on my parent table. The problem is If (Sell_amt'<'Mrp_amt) then i need to update Special_DealStatus column with 1.

Please suggest,I have googled a lot but couldn't find the answer.

Upvotes: 0

Views: 70

Answers (2)

dreamstoreality
dreamstoreality

Reputation: 21

I created a SQL fiddle for you to try out. What you are looking for is this

INSERT INTO Child_Table (`name`, `sellamt`, `mrpamt`, `specialdealstatus`)
SELECT name, sellamt, mrpamt,1
FROM Parent_Table
WHERE sellamt < mrpamt;

here is the SQL fiddle

What this does is inserts the selected values from the Parent_Table into the the Child_Table WHERE sellamt < mrpamt as you asked. It also sets specialdealstatus to one with a static value '1' placed into the SELECT statement.

    import java.sql.*;

    public class MysqlPreparedStatement
    {

      public static void main(String[] args)
      {
        try
        {
          // create a mysql database connection
          String myDriver = "org.gjt.mm.mysql.Driver";
          String mySqlServer = "jdbc:mysql://localhost/test";
          Class.forName(myDriver);
          Connection conn = DriverManager.getConnection(mySqlServer, "root", "");

      // the mysql statement
      String query = " INSERT INTO Child_Table (`name`, `sellamt`, `mrpamt`,  `specialdealstatus`)
    SELECT name, sellamt, mrpamt,1
    FROM Parent_Table
    WHERE sellamt < mrpamt ";

    // create the mysql insert preparedstatement
          PreparedStatement preparedStmt = conn.prepareStatement(query);
 // execute the preparedstatement
      preparedStmt.execute();

          conn.close();
        }
        catch (Exception e)
        {
          System.err.println("Throwing an exception!");
          System.err.println(e.getMessage());
        }
      }
    }

Upvotes: 1

paje007
paje007

Reputation: 1101

You could try something like the following with case as Andreas already mentioned.

insert into Child
select Name,Sell_amt,Mrp_amt, case when sell_amt<Mrp_amt then 1 else null end as Special_DealStatus from Parent

Upvotes: 0

Related Questions