Update Query

Documentation for `updateQuery`

The updateQuery function generates a MySQL UPDATE query with advanced options, including support for dynamic CASE statements, joins, sorting, limiting, null/default value assignments, and calculated column updates. This function is designed for developers who need flexible and efficient query building.

import {updateQuery} from "agile-query"

Function Definition

export function updateQuery<Tables extends string[]>({
    table,
    joins,
    updateData,
    where,
    nullValues,
    defaultValues,
    limit,
    sort,
    fromSubQuery,
    setCalculations,
}: {
    table: string,
    updateData?: {
        [key: string]: string | number | null | {
            case: {
                when: string;  // The condition in the WHEN clause
                then: any;     // The value to set in the THEN clause
            }[];   // The CASE structure with an array of WHEN/THEN conditions
            default: any;       // The default value for the column when no conditions match
        };
    },
    sort?: { [P in Tables[number]]?: Record<string, 1 | -1> } | Record<string, 1 | -1> | string,
    where: string,
    nullValues?: string[],
    defaultValues?: string[],
    limit?: string | number,
    joins?: JoinsType<Tables>,
    fromSubQuery?: Record<string, string>,
    setCalculations?: {    // For SET calculations (e.g., column = column + 10)
        [key: string]: string;
    }
}): string;

Parameters

Parameter	Type	Description	Required
`table`	`string`	The name of the table to update.	✅
`updateData`	`Record<string, string \| number \| null \| { case: Case[]; default: any }>`	Data to update. Supports direct assignments or dynamic `CASE` statements.	❌
`where`	`string`	The condition for selecting rows to update (`WHERE` clause).	✅
`joins`	`JoinsType<Tables>`	An array defining the `JOIN` clauses to include other tables in the update logic.	❌
`nullValues`	`string[]`	Columns to set to `NULL`.	❌
`defaultValues`	`string[]`	Columns to reset to their default values.	❌
`limit`	`string \| number`	Limits the update operation to a specified number of rows.	❌
`sort`	`Record<string, 1 \| -1>` \| `string`	Specifies sorting order of rows (`ORDER BY` clause).	❌
`fromSubQuery`	`Record<string, string>`	Subqueries for updating columns (`SET column = (subquery)`).	❌
`setCalculations`	`Record<string, string>`	Calculations for updating columns (`SET column = column + value`).	❌

`updateQuery` Function Parameters

table (string)
- Description: The name of the table to update.
- Required: Yes
- Example: 'employees'
updateData (object, optional)
- Description: An object representing the columns to update and their corresponding new values. Can be a simple column-value map or a more complex CASE WHEN structure for conditional updates.
  - Simple column-value map: Updates a column to a fixed value.
  - CASE WHEN structure: Allows dynamic column updates based on conditions.
- Required: Yes
- Example:
```
updateData: {
    salary: 50000,
    department: 'HR'
}
```
  or for conditional updates:
```
updateData: {
    salary: {
        case: [
            { when: "position = 'Manager'", then: 100000 },
            { when: "position = 'Developer'", then: 80000 }
        ],
        default: 50000
    }
}
```
where (string)
- Description: The WHERE condition specifying which rows to update.
- Required: Yes
- Example: 'id = 1'
nullValues (string[], optional)
- Description: An array of column names to set as NULL. These columns will be explicitly set to NULL in the update.
- Required: No
- Example: ['email', 'phone_number']
defaultValues (string[], optional)
- Description: An array of column names to set to their default values. These columns will be set to the default value defined in the schema.
- Required: No
- Example: ['status', 'role']
limit (string | number, optional)
- Description: Limits the number of rows to update. Adds a LIMIT clause to the query to restrict the number of updated rows.
- Required: No
- Example: 10 or '10' (limit to 10 rows)
sort: Orders rows by order_date in descending order (ORDER BY order_date DESC).
- Example:
  - { name: 1 } (sort by name in ascending order)
  - { users: { age: -1 } } (sort age in descending order for the users table)
joins:
- Adds an INNER JOIN between the orders table and the customer table.
- Joins on the condition customer.customer_id = order.customer_id.

when use on relation then use table parameter or user column name.

    [
        { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' },
        { type: 'LEFT JOIN', table: 'payments', on: 'orders.id = payments.order_id' }
    ]

   joins: [                                 // JOIN operations to filter rows for deletion
       {
           type: 'INNER JOIN',
           operator: '=', // DEFAULT '='
           customer: 'customer_id', // First table name is primary table 
           order: 'customer_id', // Second table name with column name is foreign table and foreign key
       },
   ]

fromSubQuery (Record<string, string>, optional)
- Description: An object where each key is a column name, and the value is a subquery that will be used to update that column. This can be used for complex updates where the column value comes from another query.
- Required: No
- Example:
```
fromSubQuery: {
    salary: "(SELECT AVG(salary) FROM employees WHERE department = 'IT')"
}
```
setCalculations (Record<string, string>, optional)
- Description: An object where each key is a column name, and the value is a calculation to apply to that column (e.g., column = column + 10).
- Required: No
- Example:
```
setCalculations: {
    salary: 'salary * 1.05',
    bonus: 'bonus + 500'
}
```

Example of Full `updateQuery` Call with Parameter Descriptions

const query = updateQuery<['employees', 'departments', 'payment']>({
    table: 'employees',                            // Name of the table to update
    updateData: {                                  // Data for updating columns
        salary: {                                   // CASE WHEN logic for dynamic updates
            case: [
                { when: "position = 'Manager'", then: 100000 },
                { when: "position = 'Developer'", then: 80000 }
            ],
            default: 50000
        },
        department: 'HR'                           // Regular update for department column
    },
    where: 'id = 1',                           // Condition for which rows to update
    nullValues: ['email'],                         // Set email column to NULL
    defaultValues: ['status'],                     // Set status column to its default value
    limit: 10,                                     // Limit the update to 10 rows
    sort: { created_at: -1 },                      // Sort by `created_at` in descending order
    joins: [                                       // Join with departments table
        {
            type: 'INNER JOIN',
            table: 'departments',
            on: 'employees.department_id = departments.id'
        },
        {
            type: 'INNER JOIN',
            payment: 'department_id',
            operator: '=', // DEFAULT '='
            departments: 'department_id', // First table name is primary table 
        },
    ],
    fromSubQuery: {                                // Use subquery to update salary
        salary: "(SELECT AVG(salary) FROM employees WHERE department = 'IT')"
    },
    setCalculations: {                             // Apply calculations to columns
        salary: 'salary * 1.1',
        bonus: 'bonus + 500'
    }
});

console.log(query);
// UPDATE employees 
// INNER JOIN departments ON employees.department_id = departments.id 
// JOIN payment ON payment.department_id = employees.department_id 
// SET salary = CASE WHEN position = 'Manager' THEN '100000' WHEN position = 'Developer' THEN '80000' ELSE '50000' END, 
//     department = 'HR', 
//     salary = (SELECT AVG(salary) FROM employees WHERE department = 'IT'), 
//     salary = salary * 1.1, 
//     bonus = bonus + 500, 
//     email = NULL, 
//     status = DEFAULT 
// WHERE id = 1 
// ORDER BY created_at DESC 
// LIMIT 10;

Error Handling for Missing Parameters

table or condition missing: The function throws an error if the table or condition parameters are not provided.

Example:

updateQuery({
    table: '',              // Missing table name
    updateData: { salary: 50000 },
    where: 'id = 1'
}); // Error: "The `table` parameter is required."

1. Simple Update

Update a single column with a constant value:

const query = updateQuery({
    table: 'employees',
    updateData: { salary: 60000 },
    where: 'id = 1'
});
console.log(query);
// Output: UPDATE employees SET salary = 60000 WHERE id = 1;

2. Conditional Update with CASE WHEN

Use a CASE expression for dynamic updates based on a where:

const query = updateQuery({
    table: 'employees',
    updateData: {
        salary: {
            case: [
                { when: "position = 'Manager'", then: 100000 },
                { when: "position = 'Developer'", then: 80000 }
            ],
            default: 50000
        }
    },
    where: 'id = 1'
});
console.log(query);
// Output: UPDATE employees SET salary = CASE WHEN position = 'Manager' THEN 100000
//         WHEN position = 'Developer' THEN 80000 ELSE 50000 END WHERE id = 1;

3. Update with Calculation

Apply a calculation (e.g., increase salary by 10%) on a column:

const query = updateQuery({
    table: 'employees',
    updateData: { salary: 'salary * 1.1' },
    where: 'id = 1'
});
console.log(query);
// Output: UPDATE employees SET salary = salary * 1.1 WHERE id = 1;

4. Update with Subquery in SET

Set a column's value based on a subquery:

const query = updateQuery({
    table: 'orders',
    updateData: { total_amount: '(SELECT SUM(amount) FROM order_items WHERE order_id = orders.id)' },
    where: 'status = "pending"'
});
console.log(query);
// Output: UPDATE orders SET total_amount = (SELECT SUM(amount) FROM order_items WHERE order_id = orders.id) WHERE status = 'pending';

5. Update Multiple Columns with Different Conditions

Update multiple columns, each with its own where:

const query = updateQuery({
    table: 'employees',
    updateData: {
        salary: { case: [{ when: "position = 'Manager'", then: 120000 }], default: 50000 },
        department: 'HR'
    },
    where: 'id = 1'
});
console.log(query);
// Output: UPDATE employees SET salary = CASE WHEN position = 'Manager' THEN 120000 ELSE 50000 END, department = 'HR' WHERE id = 1;

6. Update with NULL Values

Set certain columns to NULL:

const query = updateQuery({
    table: 'employees',
    updateData: { salary: 70000 },
    nullValues: ['phone'],
    where: 'id = 1'
});
console.log(query);
// Output: UPDATE employees SET salary = 70000, phone = NULL WHERE id = 1;

7. Update with Default Values

Set columns to their default values:

const query = updateQuery({
    table: 'employees',
    updateData: { salary: 80000 },
    defaultValues: ['address'],
    where: 'id = 1'
});
console.log(query);
// Output: UPDATE employees SET salary = 80000, address = DEFAULT WHERE id = 1;

8. Update with Joins

Join another table while updating:

const query = updateQuery({
    table: 'orders',
    updateData: { status: "'completed'" },
    where: 'orders.id = 1',
    joins: [
        { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' }
    ]
});
console.log(query);
// Output: UPDATE orders INNER JOIN customers ON orders.customer_id = customers.id SET status = 'completed' WHERE orders.id = 1;

9. Update with Sorting and Limit

Sort the results and limit the number of rows updated:

const query = updateQuery({
    table: 'products',
    updateData: { price: 'price * 1.2' },
    where: 'stock > 0',
    sort: { name: -1 },
    limit: 5
});
console.log(query);
// Output: UPDATE products SET price = price * 1.2 WHERE stock > 0 ORDER BY name DESC LIMIT 5;

10. Update with Multiple Joins

Update with multiple joins, complex conditions, and calculations:

const query = updateQuery({
    table: 'orders',
    updateData: { total_amount: 'total_amount + (SELECT SUM(price) FROM order_items WHERE order_id = orders.id)' },
    where: 'orders.status = "pending"',
    joins: [
        { type: 'INNER JOIN', table: 'order_items', on: 'orders.id = order_items.order_id' },
        { type: 'LEFT JOIN', table: 'customers', on: 'orders.customer_id = customers.id' }
    ]
});
console.log(query);
// Output: UPDATE orders INNER JOIN order_items ON orders.id = order_items.order_id LEFT JOIN customers ON orders.customer_id = customers.id
//         SET total_amount = total_amount + (SELECT SUM(price) FROM order_items WHERE order_id = orders.id)
//         WHERE orders.status = 'pending';