Solved

Mysql update statement or update if not exist

Posted on 2014-04-05
7
334 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 95 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 95 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…

730 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