Using If then else in Mysql

Question

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.

Oops wrong link, try this one: Have a look at the CASE expression. — Andreas
– Andreas, Commented Sep 21, 2015 at 4:24

dreamstoreality · Accepted Answer · 2015-09-21 05:58:18Z

1

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());
        }
      }
    }

edited Sep 21, 2015 at 5:58

answered Sep 21, 2015 at 4:40

dreamstoreality

215 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dreamstoreality Over a year ago

The Java is an added bonus, because you are new. Next time please include code you have already tried or worked on to show you have put some effort into the issue. I didn't do this with my first question, so I suppose I am paying it forward. :)

paje007 · Accepted Answer · 2015-09-21 04:28:48Z

0

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

answered Sep 21, 2015 at 4:28

paje007

1,1119 silver badges9 bronze badges

Collectives™ on Stack Overflow

Using If then else in Mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related