Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mysql update statement or update if not exist

Posted on 2014-04-05
7
Medium Priority
?
339 Views
Last Modified: 2014-04-06
Hi Experts

I am in the process of creating a program that inserts data using multiselect forms. My question is, how do I do a column update without overwriting the exist data in a table column? Insert does not have a 'where' clause so I cannot use that.
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
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39980226
If the data in that column is unique (and it sounds like it is, otherwise you won't be concerned about overwriting it) you can add an unique index on it and then use:
INSERT IGNORE INTO table

This will simply generate a warning instead of an error if the key already exists and the INSERT will be discarded.

HTH,
Dan
0
 

Author Comment

by:jw124210
ID: 39980356
Hi Dan

Thank you for the feedback. What I am trying to achieve is update using a primary key and where clause. The reason for this is because the there is existing data in the table, when I use update it removes all values in the updated column, I want to add to existing data.

An example table is as follows:

f_id Primary key) fruits (varchar). The table fruits already contains fruits and would want to add more fruits in that table in a specific row using a primary and without deleting existing content.

Thanks
0
 
LVL 35

Assisted Solution

by:Dan Craciun
Dan Craciun earned 380 total points
ID: 39980369
UPDATE table SET fruits = CONCAT(fruits, new_value) WHERE f_id = key

Open in new window

or
INSERT field1, field2, .., new_value, fieldn INTO table
ON DUPLICATE KEY UPDATE fruits = CONCAT(fruits, new_value)

Open in new window

new_value and key will have to come from php or whatever you're using.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 25

Expert Comment

by:TempDBA
ID: 39981148
Can you please share some data so that it will be more clearer to help?
0
 

Author Comment

by:jw124210
ID: 39981303
Hi Dan

Thanks, I can perform the update from mysql commandline, but not through JDBC. I am using JDBC and preparedstatements for updates, I changed it a bit by adding a new character.

update fruits_tbl set fruitname=concat(fruitname, "\r\norange") where  fruit_id='111';

The columns are as follows:

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

Thanks
0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 380 total points
ID: 39981547
Try changing the "" to '':
update fruits_tbl set fruitname=concat(fruitname, '\r\norange') where  fruit_id='111';
0
 

Author Closing Comment

by:jw124210
ID: 39981597
Thank you so much Dan, much appreciated, it works. I also made a few adjustments.

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

I have another question, but I post a new question for that.

Thanks.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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 Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

618 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