Mysql update query with sub query

sql

subqueries

join

performance

byAnton Shumikhin·Nov 14, 2024

Effortlessly perform an UPDATE using a subquery in MySQL through direct joining of tables within the UPDATE statement:

UPDATE table1 AS t1
JOIN table2 AS t2 ON t1.matching_column = t2.matching_column
SET t1.target_column = t2.source_column
WHERE t1.filter_column = 'filter_value';

Refit table1 to the primary table you're refreshing, table2 to the table supplying the data. Specify a common field for joining using matching_column, define update requirement using target_column, and trace the origin of new values to source_column.

Updating customer balance incorporating new transactions, for instance:

UPDATE customers AS c
JOIN transactions AS t ON c.customer_id = t.customer_id
SET c.balance = c.balance + t.amount
WHERE c.active = 1;

This manoeuvre modifies the balance in customers for solely active customers (active = 1), based on their corresponding transactions.

Aggregate data update using advanced subqueries

In scenarios where you're needing to update based on aggregated data derived from a JOIN, a subquery is what you're needing.

UPDATE competitions AS c
JOIN (
    SELECT CompetitionID, COUNT(*) AS team_count
    FROM PicksPoints
    GROUP BY CompetitionID
) AS pp ON c.id = pp.CompetitionID
SET c.number_of_teams = COALESCE(pp.team_count, 0);

We cleverly use a subquery here to first calculate team counts and then join it to the main table.

-- Don't mind me, just counting nothing (i.e., NULL values) as zero

Handling left join subqueries

Let's say you are in a situation where the relationship might not exist in both tables, the LEFT JOIN comes to rescue ensuring that you still update competitions without a corresponding entry in PicksPoints:

UPDATE competitions AS c
LEFT JOIN (
    SELECT CompetitionID, COUNT(*) AS team_count
    FROM PicksPoints
    GROUP BY CompetitionID
) AS pp ON c.id = pp.CompetitionID
SET c.number_of_teams = IFNULL(pp.team_count, 0);

The IFNULL is your careful way to handle cases where team_count could be NULL.

-- Not a team to be found? No problem, let's call it a "Bermuda triangle" competition, lol

Applying subqueries in updates

Utilizing subqueries precisely in updates can boost performance and accuracy of your data manipulation tasks monumentally.

Need a count or some other aggregation from related records before dipping into an update? Fire up those subqueries!

SET c.total_picks = (
    SELECT COUNT(*)
    FROM PicksPoints
    WHERE CompetitionID = c.id
);
# Relying on the power of subqueries to deliver the data in views land

Conditional updates

Applying conditions derived from another table has never been simpler:

SET c.status = (
    SELECT IF(COUNT(*) > 0, 'Active', 'Closed')
    FROM PicksPoints
    WHERE CompetitionID = c.id AND status = 'Pending'
);
# If you blink, you'll be LEFT OUTER JOINed

Performance tips

When clicking into gear with subqueries:

Opt for selecting only essential columns for enhanced results.
Firm up the connection of your subquery with a correlation condition with the outer query.
Keep your eyes peeled for proper indexing to speed up the joins and conditions.

Practical approaches to subqueries

When visualizations don't quite cut the mustard, here are more pragmatic takeaways:

Use aliases

Through AS, aliasing can help draw a clear line between tables and columns, especially for intricate queries:

# Alias subquery for easier reference
) AS subquery_alias ON main_table.id = subquery_alias.related_id

Syntax verification

Meticulously check syntax to avoid any inconveniences and errors that can pop up due to complex nesting:

# Ensure subquery's SELECT statement includes 'CompetitionID' for JOIN match

-- You get an alias! You get an alias! Everyone gets an alias!

Comprehensive examples

A demonstration link can provide hands-on, testable examples verifying the concept:

Check the [working demo](#) for a detailed example of multi-table updates with subqueries.

explain-codes / Sql / Mysql update query with sub query

Linked

Aggregate function in an SQL update query?



Using GROUP_CONCAT on subquery in MySQL



Mysql Select Records for Duplicates Using Multiple Columns



Mysql, update multiple tables with one query



Why does MySQL report a syntax error on FULL OUTER JOIN?



Select count of rows in another table in a Postgres SELECT statement



Counting number of joined rows in left join



Aggregate data update using advanced subqueries Handling left join subqueries Applying subqueries in updates Practical approaches to subqueries