Adding data to row without replacing existing data.

[bugboy]

Am i able to an INSERT into a row that adds to and doesn't replace the current data?

I know i can SELECT the data and concat it with the new data in PHP then INSERT it but i was wondering if i can do it with SQL only? I haven't been able to find anything on it...

Thanks

[bugboy]

well duh.. i've realized i should be using UPDATE.. but still don't know how to add to existing row and not replace it.

[dinox]

well i don't quite understand what do you mean by adding without rewriting but i guess you mean something you can use in mysql alone as shortcut ...

for example for incrementing numbers columns...try..
[CODE=mysql]UPDATE table SET column = column + 1 .. etc[/CODE]

[bartonc]

well duh.. i've realized i should be using UPDATE.. but still don't know how to add to existing row and not replace it.

That's where unique IDs and the WHERE clause come in. For example:[CODE=mysql]UPDATE hetap_data.agen cies SET agencyName='In House' WHERE agencyID = 4;[/CODE]works on a table that looks like this:[CODE=mysql]CREATE TABLE `hetap_data`.`a gencies` (
`agencyID` integer unsigned NOT NULL auto_increment,
`stateID` integer unsigned NOT NULL default '0',
`agencyName` varchar(45) NOT NULL default '',
PRIMARY KEY (`agencyID`)
) ENGINE=InnoDB;[/CODE]

[mwasif]

You can concatenate the value in MySQL too, you don't need to process data in PHP (or in client side code).

I assume you have a column in a row with value 'Agree' and want to update it to 'Not Agree'. The following query will change the value from 'Agree' to 'Not Agree' i.e.
[CODE=mysql]UPDATE table SET
opinion = CONCAT( 'Not ', opinion)
WHERE id=1[/CODE]
Now the column will have the value 'Not Agree'.

And if you want to append the new value at the end, change the parameters order in the CONCAT() function.
[CODE=mysql]UPDATE table SET
opinion = CONCAT( opinion, ' Not')
WHERE id=1[/CODE]

Are you looking for this?

Checkout MySQL manual to know more about UPDATE statement.

[bugboy]

Yes mwasif, CONCAT is what i was looking for, thanks! And for the other replies as well, all very useful! i love the scripts!

bartonc: What have you done with the table name.. it looks like you have concatenated two tables?

'hetap_data'.'a gencies'

...or is this just a normal table name with a period in it?

[mwasif]

bartonc: What have you done with the table name.. it looks like you have concatenated two tables? ...or is this just a normal table name with a period in it?

hetap_data is a database name in which the table agencies exists. You can use multiple tables from multiple database in a single query. But for this you have to mention database names with table names as bartonc did.

[bugboy]

ah of course, Thanks guys!

[mwasif]

You are welcome, glad to help you.