Updating a record with SQL 1

[mych]

Hi,

I am trying to improve my SQL update as I believe it is not efficient.

The table contains various fields to deal with incidents. The one field "Updates" is a concatanated field. In other words each time there is a new update to the incident the field gets updated by adding the new update to what already exists in the "Updates" field.

Currently the Web/Sql system I have designed works as such...
I have a SQL Select that reads in the value of the "Updates" field.
I have a form that will be completed to give a NewUpdate.
I have an strAudit which comprises of Date/Time and UserName.
I then add these together to give NewUpdate = strAudit & NewUpdate.
I then add the value of "Updates" and the NewUpdate together by Updates = Updates & NewUpdate
Finally I have a SQL Update that updates "Updates" field with the new value

What I would like to do if possible is something like

UPDATE "table_name"
SET Update = Updates & [strAudit & NewUpdate]
WHERE {condition}

Is this possible? my attempts have given me errors and I have not found any SQL syntaxes that give this result.

Is it a case that I have to read, then add and then update?

Thanks
Mych

 

[rac2]

This can be done in an UPDATE statement.

Dim strSQL, strAudit, NewUpdate, strWidgetID

strSQL = "UPDATE my_table_name SET Updates = Updates + " & strAudit & NewUpdate & " WHERE widget_id = " &  strWidgetID

But you must use the correct column name, Updates, and the SQL Server operator for concatenation, + .

You can build the UPDATE statement in the web page script or you can pass the data as parameters to a stored procedure. The above shows a line of VBscript for building the UPDATE query in a web page script.

 

[mych]

Thanks for this....

As I'm more used to VB and was trying all combinations with & I didn't think about + being used in SQL.

 

[mych]

Have tried the above approach and it seems I may have ommited to disclose an improtant fact... the field I'm updating is an ntext field...

I'm getting the following error

/* Error : "Invalid operator for data type. Operator equals add, type equals ntext.
" */

Is there anything else I can try....

The SQL I used was...

UPDATE D_INCIDENTS
SET Status = '2', TT = '0',
Updates = Updates + 'Update to test Update + Audit & NewUpdate code', InUseBy = '0000000'
WHERE IncidentRef = '1489';

 

[rac2]

You might try

...
SET ... ,
 Updates = CONVERT(VARCHAR(8000), Updates) + 'yada yada',
...

Also take a look at Books Online topic "text and image data types" for special methods used to handle text datatype.