Java Insert Or Update in SQL logic

Question

I have a table as shown below:

mysql> select *  from category;
+-------------+----------+------+------+------+------+------+------+------+------+------+-----------------+
| category_id | T1       | T2   | T3   | T4   | T5   | T6   | T7   | T8   | T9   | T10  | vendor_brand_id |
+-------------+----------+------+------+------+------+------+------+------+------+------+-----------------+
|          12 | Popcorn      | rgp  | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 3000            |

I am trying to insert a new Row as Popcorn Bucket so that it looks this way:

mysql> select *  from category;
+-------------+----------+------+------+------+------+------+------+------+------+------+-----------------+
| category_id | T1       | T2   | T3   | T4   | T5   | T6   | T7   | T8   | T9   | T10  | vendor_brand_id |
+-------------+----------+------+------+------+------+------+------+------+------+------+-----------------+
|          12 | Popcorn      | rgp  | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 3000            |
|          13 | Popcorn      | Bucket  | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 3000            |

But this is updating the existing row.

Could anybody please tell me what's wrong?

String vendor_brand_id ="3000";
String reqstr ="Popcorn";
String value="Bucket";

sqlselect = "select category_id  from category where T1 = ? AND T2!=NULL  AND vendor_brand_id = ?";
selectpst = dbConnection.prepareStatement(sqlselect);
selectpst.setString(1, valuess[0]);
selectpst.setString(2, vendor_brand_id);
resultset = selectpst.executeQuery();

if(resultset.next())
{
    sql=    "select category_id   from category where T1 = ? AND T2=?  AND vendor_brand_id = ?";

    selectpst2 = dbConnection.prepareStatement(sql);

    selectpst2.setString(1, valuess[0]);
    selectpst2.setString(2, value);
    selectpst2.setString(3, vendor_brand_id);
    resultset2 = selectpst2.executeQuery();

    if(resultset2.next())
    {

    }
    else
    {
        sql = "Insert into category (T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,vendor_brand_id) values ('"+valuess[0]+"','"+value+"',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"+vendor_brand_id+") ";
        updateStatement.executeUpdate(sql);
    }
}
else
{
    sql = "update category set T2 = '"+value+"' where T1 ='"+valuess[0]+"'   AND vendor_brand_id = "+vendor_brand_id+" " ;
    updateStatement.executeUpdate(sql);
}

Thank you ,but as per table design there is nothing i can depend on — user663724
– user663724, Commented Oct 27, 2014 at 20:48
First thought: instead of " AND T2!=NULL", try " AND T2 IS NOT NULL" — Chris Forrence
– Chris Forrence, Commented Oct 27, 2014 at 20:48
@ChrisForrence, it is a reason why record is updated but not inserted. Why you do not add your comment as answer? — Nicolai
– Nicolai, Commented Oct 27, 2014 at 20:53
@Nicolai - Because it had been a comment; it had only been a thought. I also didn't have any sources prepared. — Chris Forrence
– Chris Forrence, Commented Oct 27, 2014 at 20:56

Chris Forrence · Accepted Answer · 2014-10-27 20:53:21Z

1

MySQL handles NULL in a rather special manner; from the article: "You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL"

Try changing your first SELECT statement:

/* Old */
select category_id  from category where T1 = ? AND T2!=NULL  AND vendor_brand_id = ?

/* New */
SELECT category_id FROM category WHERE T1=? AND T2 IS NOT NULL AND vendor_brand_id=?

answered Oct 27, 2014 at 20:53

Chris Forrence

10.2k11 gold badges50 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

Java Insert Or Update in SQL logic

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related