Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Mysql update statement or update if not exist

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
jw124210
Asked:
jw124210
  • 3
  • 3
2 Solutions
 
Dan CraciunIT ConsultantCommented:
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
 
jw124210Author Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
TempDBACommented:
Can you please share some data so that it will be more clearer to help?
0
 
jw124210Author Commented:
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
 
Dan CraciunIT ConsultantCommented:
Try changing the "" to '':
update fruits_tbl set fruitname=concat(fruitname, '\r\norange') where  fruit_id='111';
0
 
jw124210Author Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now