Solved

Mysql update statement or update if not exist

Posted on 2014-04-05
7
329 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
  • 3
  • 3
7 Comments
 
LVL 34

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 34

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 34

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now