How can I merge data within a DB table by a SQL statement?

Question

I have an interesting problem for Interbase. I have a record set which amongst other things has a data entry field and a timestamp for each record. I would like to be able to copy the timestamp from records so that at the end of the day the final field will read along the lines of:

TIMESTAMP <Carriage return> <carriage return> Original data in field

Thus the final appearance when the field is displayed is:

time of entry

data that was entered

Sadly, this must be accomplished at the database level as I do not have access to the front end software.

The only solution I have been able to come up with is do to a select, use custom software to perform the merge and then generate the appropriate SQL, line by line, to update the record. As there are several hundred records per day this is rather slow, so a SQL based solution would be ideal.

In the past I have succeeded in adding fixed data to the end of a string using || for concatenation with an UPDATE instruction (data||extra information which is the same for all records") but have trouble making this work in the format:

result of search||original data

Any help would be much appreciated.

Answer 1

UPDATE FOO_TABLE
  SET DATA_ENTRY_FIELD = DATA_ENTRY_FIELD || TIMESTAMP_FIELD
WHERE ...;

This should work (provided DATA_ENTRY_FIELD has room for the concatenation). Please be more specific about what problems you're having if it doesn't work.

Answer 2

Craig's answer should work, although if you want the carriage returns, you'll need to insert those too:

UPDATE  foo_table
SET     data_entry_field = timestamp || '\n\n' || data_entry_field
WHERE   ...

(Assuming that Interbase accepts \n as a carriage return -- which should really be just \r or even \r\n on Windows, but \n is fairly standard.)

MySQL doesn't recognize the || concatenation operator, and so you'd have to use the CONCAT() function instead. Interbase might be the same.