Using 'update rows' feature in database management

[astute_cockroach]

Hi,

How do you use the ‘update rows’?

I have a list of objects that are stored in the database, each in its own row. I am able to retrieve it, but I don’t know how to update all these rows (I attached a screenshot as an example).

Can someone please help?

[icaro]

Hey @astute_cockroach. This will update all the rows that satisfy the condition in the Filter section with the same values.

[astute_cockroach]

Hello @icaro yes… but how do I filter so all objects will go to the right row? I don’t know how to that part. I tried to do it for some hours now with co-pilot and couldn’t find the answer and have now even exceeded the daily limit.

Could you please direct me what I need to do to separate each ID for each row in the database?

Thank you.

[icaro]

Do you want to update each ID to a different value? This type of query is specifically used to update all rows to the same values.

[icaro]

Click Add rule to add the filter rules that are going to be used to get the rows that will be updated.

[astute_cockroach]

Yes, I need to update each ID with its own row in the database. So for example

(3) [Object, Object, Object]

1. 0: Object
   a. id: “1”
   b. title: “Title 2”
2. 1: Object
   a. id: “2”
   b. title: “Title 1”
3. 2: Object
   a. id: “3”
   b. title: “Test”
   And each object got its own row in the database and I want to update all these objects by looking up their ID and update all the titles (for example).

[astute_cockroach]

I am now having. this error all the time

1. {error: Object}
2. error: Object
   i. statusCode: 400
   a. message: “multiple assignments to same column “id””

[astute_cockroach]

I tried things like this in the Add rule to filter rules… but it doesn’t work…

function filterRankById(id) {
return state.rank.filter(item => item.id === id);
}

[icaro]

Is the number of objects dynamic or is it static?

[astute_cockroach]

The objects are dynamic, user generated.

[icaro]

Have you tried writing a custom SQL query to get this done? You can select the query type Custom SQL query for write and write the query to update multiple rows.

[astute_cockroach]

I haven’t tried the custom SQL query earlier… but am testing it out right now after your suggestion.

I am not sure what I am doing wrong, but no matter what I do I got an Invalid SQL string error… even with a simple SELECT * FROM public.test; I got that error.

But now I am not sure if this is a general problem going on right now at Plasmic or I am doing something wrong.

[icaro]

We only allow dynamic values in the “values” part of the expression for security reasons.

Try writing SELECT * FROM your_table directly on the editor.

You can do dynamic values like

SELECT * FROM your_table WHERE column = {{ DYNAMIC_EXPRESSION }}

[astute_cockroach]

okay let me try that…

[astute_cockroach]

For people who are following this thread and wondering what the file was… deleted it by accident when editing the post

[astute_cockroach]

Even a simple SELECT * FROM your_table gives me an Invalid SQL string error. Tried it with Custom SQL query to fetch rows and Customm SQL query for write operations but both give me the same error.

I tend to think that the Custom SQL query function has a bug. Can you maybe test and confirm if the SELECT * FROM your_table function works on your side? If it does work on your side, then at least I can leave that option out.

[icaro]

You are editing everything inside the dynamic expression editor. Exit the expression and write directly on the static expression editor

[astute_cockroach]

Thank you @icaro, I can confirm that SELECT * FROM your_table does work in the static expression editor.

The rest unfortunately not yet, still getting the Invalid SQL string / Missing required field: Query error. I will dig deeper to try to get it to work.