Saving data: Only insert, update not working

Hi

I want to save form data into a database table. Everytime I change the data in the frontend and save, CF6 uses INSERT instead of UPDATE.
INSERT is OK if a new user fills in the empty form. For existing users it should update (data in form is loaded automatically if user is logged in).

In the database table the first column is named "id" and is Primary key.

Is there anything else I need to add?
​
Thanks a lot!
​
​
Regards
Patrick
​
​


​

   [_success] => Data saved successfully
            [log] => Array
                (
                    [0] => INSERT INTO `d4r5_jsn_users` (`firstname`, `lastname`)  values  ('Mickey', 'Mouse Test');
                )

            [var] => Array
                (
                    [firstname] => Mickey
                    [lastname] => Mouse Test
                    [id] => 2138
                )

        )

)

Hi pat01 ,
​
Do you have the user ID in your table - I assume that you do, then you should use that in the Conditions box to look for and update that record.
​
Bob

Hi Bob


Yes, column "id" has the user ID stored. I can't figure out the syntax to use in the conditions field.

id:{user:id}

is not working (but it works in the data read action, tough).

Patrick

Either try Insert - Duplicate Key Update, or
​
Have a read data action looking for the row where id:{user:id}
​
IF FOUND: Have a save data set to update
NOT FOUND: Have a save data set to insert

Duplicate key update does not update either, but it does insert.
I already have a read action. It does fill the form fields with the user data. So the ID must be known.

Explanation in CF6:
Select the whether to insert or update or let it be decided based on the primary key value passed and the update conditions.
​
Column "id" in the database is = Primary key. I don't understand why CF6 does not use it.

So if you do the second thing I said, it will work the way you want.

I did try as you said, but it is still not working. Setting the write action to "Update" returns
​

1065 - Query was empty 

custom_code19 returns a simple text to prove, that the record was found. So everything OK to this point. But after save_data16 does INSERT if set to "Automatic" or "Insert - duplicate key update". Or it throws "Query was empty" if set to UPDATE.
​

The SECOND thing I said. All the stuff AFTER the first suggestion.

Sorry, I don't really understand. What do you call second thing?
​
This?:
​
Have a read data action looking for the row where id:{user:id}
​
IF FOUND: Have a save data set to update
NOT FOUND: Have a save data set to insert
​
That's what I did.

And did you set the update condition? And the Data Override on Update? Because both of those need to be filled in.

I found the solution:
​
Simply add

{data:}

to the data provider.

@healyhatman
​
Yes, please see my printscreen above ;-)

I wouldn't do that if I were you, it can sometimes add *everything* to the database save action. Just fill in the update/insert sections with just the fields and the data you want to add/update.

OK, thanks a lot for the hint!