?
Solved

PreparedStatement update does not update on NULL values

Posted on 2014-04-06
3
Medium Priority
?
56 Views
Last Modified: 2016-06-04
Hi Experts

I am trying to update a field with NULL values without success, but If I put an empty String the update works. The problem with putting an empty string is - my column field "fruitname" accepts unique values. I am using JDBC. see below what I am trying to do.

update fruits_tbl set uxservernames=concat(fruitname, \r\n'" + fruitname + "') where fruit_id=" +fruit_id)

The columns

fruit_id (INT) PRK | fruitname (VARCHAR(255) | fruitdescr VARCHAR(255)|

The fruitname column can hold a NULL value.

Thank you.
0
Comment
Question by:jw124210
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 380 total points
ID: 39981728
CONCAT returns NULL if any of the operators are NULL.

If you want to avoid this, us
CONCAT_WS('\r\n', fruitname, 'peach')

CONCAT_WS will simply skip any NULL values.

HTH,
Dan
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39982795
In my experience MySQL NULL does not match an empty string of zero length.   However if the columns are defined NOT NULL DEFAULT '' an empty string will match.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question