Hi, I’m still really new at SQL so I have a question. I see all kinds of tutorials about INSERT, UPDATE and DELETE statements, but they’re not real world examples. Let’s take insert for an example. All the tuts that I have read assume no record already exists for the entry that we’re inserting. How do you handle an INSERT when the record already exists? Is this where sub queries come into play? thanks.
It does? Hmm, I never noticed that. I’ll have to check it out.
The default textbox values in the form all come from the database.
A typical example could be a user wanting to change the price of a product. After the user clicks the edit button for that product a form is presented with all the current values for that product. But if the user clicks the submit button without actuially changing any of the values in the form then MySQL throws an error because it appears it doesn’t like trying to update a record unless at least 1 value in that record is being changed.
I can either store all the textbox values in variables on page load and onsubmit check to make sure at least one of the values in the textboxes is now different to the original values or I can do a select query to see if any changes to any of the columns for that record are being requested. If mysql_num_rows() returns a value > 0 then I don’t run the update query because that tells me no changes have been made to any of the values in the form’s textboxes.
It would be nice if MySQL simply updated all the columns in the record whether the user actually changed any values or not in the form.
At least he knows that updating with the same values doesn’t give an error 
If the OP wants/needs more help, he should first respond to Rudy’s post:
Thanks guido2004.
I ran your script on my database and it worked as well. So at least I know there is nothing wrong with my XAMPP.
And…aaaaahhhhhhhhhhh…after all this time, I think the penny has finally dropped :headbang: :headbang:
I have always used mysql_affected_rows() to check if an update, delete or insert has been successful. If no table values are actually changed by any columns in the update command then mysql_affected_rows() will equal 0 and so I output an error message.
But I really should just be checking if mysql_query() is returning a value of true or false because whether any column values are actually changed or not by the update doesn’t matter because mysql_query() will return true in both cases. It will only return false if a database error occurred.
Problem solved
ps…but I’m not sure if this helps the OP at all though :-/
I don’t know. Just tested it, and it gave me no errors.
// Connecting and selecting database
$link = mysql_connect(xxx, xxx, xxx) or die("Impossible connecting to DB - error: ".mysql_errno()." - ".mysql_error());
mysql_select_db(xxx) or die("Impossible selecting DB - error: ".mysql_errno()." - ".mysql_error());
// drop table
$query = "
DROP TABLE IF EXISTS test_Employee
";
$result = mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()."<br/>Query:'$query'");
// create test table
$query = "
CREATE TABLE IF NOT EXISTS test_Employee (
`idEmployee` VARCHAR(45) NOT NULL ,
`Name` VARCHAR(255) NULL ,
`idAddresses` VARCHAR(45) NULL ,
PRIMARY KEY (`idEmployee`)
)
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin
";
$result = mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()."<br/>Query:'$query'");
// add data
$query = "
INSERT INTO test_Employee
VALUES('test', 'test', 'test')
";
$result = mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()."<br/>Query:'$query'");
// do an update with the same data
$query = "
UPDATE test_Employee
SET
Name = 'test'
, idAddresses = 'test'
WHERE idEmployee = 'test'
";
$result = mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()."<br/>Query:'$query'");
echo "DONE!";
Not sure what you mean 
If you mean I should specify which language I am using to submit the query, then yes I am using PHP.
But I assumed (maybe incorrectly) that this “feature” was a MySQL thing and not a PHP thing.
To clarify, I have an “Edit Product” html form which displays by default in each textbox the current data for each column in the products database table. If the user clicks the submit button without changing the values in any of the textboxes then MySQL throws an error when I run the update query. That is why I do a select first to see if a record with all the current values in the form’s textboxes exists before doing the update.
If the user changes the value in say just 1 out 10 textboxes then the update query works fine.
Hopefully there is a more efficient way of checking if the user has actually changed any values in the form before submitting the form data to update the database.
there are several scenarios, pick the one you want
- an INSERT for a row that already exists is an error
- an INSERT for a row that already exists should be ignored
- an INSERT for a row that already exists should update only columns values that have changed
you should filter this condition with your application language (php?)
I’m using XAMPP and it does on my installation.
When I first noticed the problem some time ago now, I spent some time googling for a solution. All I could find was others experiencing the same problem but no-one had a solution other than the ones already mentioned earlier.
It’s not a big deal really. I just can’t understand why MySQL won’t update a record unless at least 1 value in that record is being changed by the update command.
I’m pretty sure that when I was using Oracle, in what seems now to be a previous life time, you could update records even if no values were being changed in a record as described in my earlier scenario.
Maybe there is something wrong or not configured correctly with my XAMPP.
if the user hasn’t made any changes to the values in the form fields, where did those form field values come from? you must’ve previously queried the database, pulled up a row, and displayed those values
so you have already retrieved a row, and now all you need to do is use php to check to see whether any of the values has changed, before submitting the update – you do not need to re-query the database again, right?
I get the same problem when a updating a table record when the user has clicked the submit button without making any changes to any if the input boxes in the html form.
MySQL throws an error when I try to update a record that doesn’t have any new values.
I haven’t been able to find a quick solution so what I do now, and it’s not really much extra coding, is run a select query first to see if the record exists exists and if it does then I don’t proceed with the update.
For example…
$isProductUpdated = $prodMgr->updateProduct($prodId,$prodName);
//and in the class file
public function updateProduct($prodId,$prodName) {
//check if record exists
$query = 'select * from tblproducts where fldProdId = "'.$prodId.'" and fldProdname = "'.$prodName.'"';
if(mysql_num_rows(mysql_query($query,$this->$conn)) > 0) {
//nothing to update
return true;
}
//proceed with the update code from here
}
I haven’t included any error checking/sanitisation in order to KISS.