which SQL statement to use? To modify data?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
crazytopu
Forum Contributor
Posts: 259
Joined: Fri Nov 07, 2003 12:43 pm
Location: London, UK
Contact:

which SQL statement to use? To modify data?

Post 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?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

UPDATE TableName SET Col2 = '$col2', Col3 = '$col3' WHERE YourPriKey = 3
evilMind
Forum Contributor
Posts: 145
Joined: Fri Sep 19, 2003 10:09 am
Location: Earth

Post 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';
Post Reply