but if is already present a record for these "name" and "surname",
I have to append the string 'Double questionarie'
to the already present value in the UserNote field.
How can I accomplish this task?
Thanks a lot in advance.
Luigi
PS
I'm using SQL Server 2008 R2, so some features are not available.
BEGIN TRANSACTION
INSERT INTO Notes (name, surname, dateref, UserNote)
SELECT 'Bob', 'Fedd', GETDATE(), 'Double questionarie'
WHERE NOT EXISTS
(
SELECT *
FROM Notes
WHERE name = 'Bob'
AND surname = 'Fedd'
)
IF @@ROWCOUNT = 0 -- No insert, record already exists?
BEGIN
UPDATE U
SET Notes = COALESCE(Notes + '. ', '') + 'Double questionarie'
FROM Notes AS U
WHERE name = 'Bob'
AND surname = 'Fedd'
IF @@ROWCOUNT <> 0 RAISERROR(... ) -- Something bad happened! Perform ROLLBACK
END
COMMIT
Various discussion points on what, if any, locks to use between the INSERT attempt and the UPDATE ...
P.S. If UPDATE is likely to be MORE common than INSERT I would swap the order of the statements (UDPATE first, if ZeroRows THEN INSERT) for better performance
IF EXISTS(SELECT 1 FROM dbo.Notes WHERE name = 'Bob' AND surname = 'Fedd')
BEGIN
UPDATE dbo.Notes
SET UserNote = ISNULL(UserNote, '') + 'Double questionarie'
WHERE name = 'Bob' AND surname = 'Fedd';
END /*IF*/
ELSE
BEGIN
INSERT INTO dbo.Notes (name, surname, dateref, UserNote)
VALUES('Bob', 'Fedd', GETDATE(), 'Double questionarie');
END /*ELSE*/