Skip to main content

New Visual Query - Update Query Generation

The Update Query Generation interface in the visual query builder allows users to modify existing data in their database by providing a user-friendly way to define update conditions and set new values.

In the provided image, we are working with the billing table and performing an update operation where certain values will be modified based on specified conditions.

Key Components:​

  1. Table Selection:

    • The interface shows the billing table selected for the update query.
    • In this example, we are working with fields like id, pk, companyid, invoiceid, and more.
    • Users can select which fields they want to update, ensuring that only relevant data is modified.

  2. Where Clause:

    • The Where tab defines the conditions under which the update will occur.
    • In this example, the update will only happen for records where the pk field is equal to 1.
    • This ensures that the system updates only the specific rows matching the condition, preventing accidental changes to other data.
    • You can add more complex conditions by clicking the + button to add additional conditions, allowing for more targeted updates.

  3. SQL Query Tab:

    • After defining the conditions and the values to be updated, the SQL Query tab will automatically generate the corresponding SQL code for the update.
    • Example: 
      UPDATE billing 
      SET paymentstatus = 'success'
      WHERE pk = 1;
    • This SQL query updates the paymentstatus column of the billing table to 'success' for rows where pk = 1.

  4. Validation Result Tab:

    • Before executing the update query, it's a good practice to validate it. The Validation Result tab allows you to verify that your query will execute correctly, ensuring data integrity.

How It Works:​

  • In the Update tab, users select the fields they want to update.
  • The Params section is where you specify the new values for the selected fields.
  • The Where clause lets you define the conditions that must be met for the update to occur.
  • The SQL Query tab shows the generated SQL code based on your inputs.
  • Use the Validation Result tab to check for potential errors before running the query.

Benefits:​

  • No SQL Knowledge Needed: You don't need to write SQL code manually; the system handles it for you based on your visual selections.
  • Real-Time Query Generation: As you make changes to the fields and conditions, the query updates in real-time, ensuring that you always see the final SQL query.
  • Error Prevention: By using the Validation Result tab, users can ensure that the query is structured correctly and won't cause database errors.

Tip: Before executing the query, double-check the conditions in the Where clause. Ensure that you are targeting the correct rows to avoid unintended data modifications.