Solved

Mysql update statement or update if not exist

Posted on 2014-04-05
7
335 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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