Page 1 of 1

which SQL statement to use? To modify data?

Posted: Tue Jan 06, 2004 10:54 pm
by crazytopu
I wanna modify the values of a row in my table called book.

When i insert a new row i use

Code: Select all

INSERT INTO TABLE(Column1, Column2....) VALUES (23,'topu');
But when i want to modify the value of a row from a GUI mode and then want to save the new value...which statement i do use?

If i use the above statement it does not let me to do so..coz primary key cannot be duplicated. so, when i want to change a row where the primary key value is 3...i got the existing data...make a change and then when i want to save the value using INSERT........................statement...it does not change the value..as primary key value 3 already exists.

So, it cannot overwrite the existing value..

Which statement to use then?

Posted: Tue Jan 06, 2004 11:13 pm
by microthick
UPDATE TableName SET Col2 = '$col2', Col3 = '$col3' WHERE YourPriKey = 3

Posted: Tue Jan 06, 2004 11:19 pm
by evilMind
Well if you are looking to update information then you could use either the "UPDATE ... WHERE" syntax or the "REPLACE [INTO] ... WHERE" syntax.

See [mysql_man]REPLACE[/mysql_man] And [mysql_man]UPDATE[/mysql_man] for more information

eg,

Code: Select all

REPLACE INTO `yourTableName` SET `column1`='column1_value', `column2`='column2_value` WHERE `primaryKey`='primaryKeyValue';
The above will delete the row if a match is found, then insert the new data. (Note that the old data is removed first so if you don't change every column then you end up with blank columns).

You can also use the UPDATE syntax:

Code: Select all

UPDATE `tableName` SET `column1`='column1Value', `col2`='col2_value'
This will update the ENTIRE table's columns column1 and col2 to the values you specify in the statement.

If you want to update only one column for a particular record then you must give a restriction (WHERE foo = bar) eg

Code: Select all

UPDATE `tableName` SET `column1`='column1_value' , `col2`='col2_value' WHERE `thisPrimaryKey`='thisExactValue';
That will update the table `tableName` setting the columns column1 and col2 to the values specified only where the match is found.

Note with either of the two queries you can have more than (or less than) two `col`='value' sets
eg

Code: Select all

UPDATE `tableName` SET `column1`='column1_value' , `col2`='col2_value' , `col3`='value' , `col4`='value' WHERE `thisPrimaryKey`='thisExactValue';

or

UPDATE `tableName` SET `column1`='column1_value'  WHERE `thisPrimaryKey`='thisExactValue';