Insert and update/append to existing data.

[RosieGp]

Hi,
I have the following Insert query: (all this data is coming from a form)
<cfquery name="insertdata" datasource="MainDB">
INSERT INTO newUsersTable (NewUserTodayDate, UserID, UserFirstName, UserLastName, UserAddress , UserPhoneNumber, UserComments) VALUES
('#DATEFORMAT(Now(), "mm/dd/yyyy")#', '#form.UserID#', '#form.UserFirstName#', '#form.UserLastName#', '#form.UserAddress#', '#form.UserPhoneNumber#', '#form.serComments#')
</cfquery>

Question: How do I change this query to check if a record already exist for the UserID entered, then append to existing data and if UserID does not exist insert data in the table. also if the UserID already exists and i'm appending data, i want some kind of new line character so that the appending data is in a new line.
For ex: UserComments
Joe bought a coverage for $100 on 10/12/12.
Joe is changing his coverage to $500 on 12/12/12.

Not together: Joe bought a coverage for $100 on 10/12/12. Joe is changing his coverage to $500 on 12/12/12.

UserID is not my primary key and not autonumber. it is already existing IDCard numbers of the users.
ANy help is appreciated. Thanks in advance.

 

[bborissov]

IF EXISTS(SELECT * FROM NewUsersTable WHERE UserId =  '#form.UserID#')
   UPDATE NewUsersTable SET FieldName1 = ValueFromYourForm1,
                            FieldName2 = ValueFromYourForm2
 ...
ELSE 
    INSERT INTO newUsersTable (NewUserTodayDate, UserID, UserFirstName, UserLastName, UserAddress , UserPhoneNumber, UserComments)
    VALUES
    ('#DATEFORMAT(Now(), "mm/dd/yyyy")#', '#form.UserID#', '#form.UserFirstName#', '#form.UserLastName#', '#form.UserAddress#', '#form.UserPhoneNumber#', '#form.serComments#')

Borislav Borissov
VFP9 SP2, SQL Server

 

[philhege]

The best approach is to use a stored procedure, not an ad hoc SQL statement. You can encapsulate the logic in the SP, and simply pass the information as parameters.
Think about your data design, also. If you want comments to be retained ongoing and to be treated as units, you should hold them in a Comments table.

-----------
With business clients like mine, you'd be better off herding cats.

 

[RosieGp]

Thanks for replies.
BBorisson - how do i write it in access. my table is in access... and i'm using coldfusion (CFM) to call this query.

I tried the following but it does not work: the record is added to Database even if the ID exists.

<cfquery name="GetData" datasource="MainDB">
SELECT * FROM NewUsersTable
<cfif IsDefined("form.ID") and "form.ID" eq "NewUsersTable
.ID">

<cfquery name="updatedata" datasource="MainDB">
UPDATE tewUsersTable 
SET UserAddress = #session.UserAddress#
</cfquery>

<cfelse>
<cfquery name="insertdata" datasource="MainDB">    INSERT INTO newUsersTable (NewUserTodayDate, UserID, UserFirstName, UserLastName, UserAddress , UserPhoneNumber, UserComments)
    VALUES
    ('#DATEFORMAT(Now(), "mm/dd/yyyy")#', '#form.UserID#', '#form.UserFirstName#', '#form.UserLastName#', '#form.UserAddress#', '#form.UserPhoneNumber#', '#form.serComments#') 
</cfquery>
</cfif>
</cfquery>

Please see if you can help.

 

[SQLBill]

If you are using MS Access, you should be posting your question in one of the MS Access forums here, not in the Microsoft SQL Server forum. While Access' SQL (Jet-SQL) is similar to SQL Server's T-SQL, they aren't completely compatible. So any solution we may give you here might not work in Access.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

This has been posted in the ColdFusion forum, and rightfully so, because the root cause of the problems is the CF code, not the SQL statements.

-----------
With business clients like mine, you'd be better off herding cats.