sql - Update multiple rows in a table from another table when condition exists

Sql - Update multiple rows in a table from another table when condition exists

To update multiple rows in a table from another table when a specific condition exists, you can use the UPDATE statement with a JOIN clause. Here's a general example:

Assuming you have two tables, source_table and target_table, and you want to update rows in target_table based on a condition involving a common column, you can do something like this:

UPDATE target_table SET target_column1 = source_table.source_column1, target_column2 = source_table.source_column2 FROM source_table WHERE target_table.common_column = source_table.common_column AND your_condition;

Replace target_table and source_table with your actual table names. Adjust target_column1, target_column2, source_column1, source_column2, common_column, and your_condition according to your specific use case.

Here's a more concrete example:

UPDATE orders SET orders.shipped_date = shipments.shipped_date FROM shipments WHERE orders.order_id = shipments.order_id AND shipments.shipped_date IS NOT NULL;

In this example:

  • orders is the target table.
  • shipments is the source table.
  • We are updating the shipped_date column in the orders table based on the order_id column.

Make sure to test your UPDATE statement on a small dataset or in a safe environment before applying it to a production database to avoid unintended consequences.

Examples

  1. "SQL Update multiple rows with matching condition"

    • Update multiple rows in a table from another table where a specific condition is met.
    UPDATE table1 SET column1 = table2.column1, column2 = table2.column2 FROM table2 WHERE table1.id = table2.id AND your_condition;

  2. "SQL Update multiple rows based on a join condition"

    • Update multiple rows using a join condition with another table.
    UPDATE table1 SET column1 = table2.column1, column2 = table2.column2 FROM table1 JOIN table2 ON table1.id = table2.id WHERE your_condition;

  3. "SQL Update with subquery for multiple rows"

    • Use a subquery to update multiple rows based on a condition.
    UPDATE table1 SET column1 = ( SELECT column1 FROM table2 WHERE table2.id = table1.id ) WHERE your_condition;

  4. "SQL Update multiple columns with CASE statement"

    • Update multiple columns using a CASE statement when a condition exists.
    UPDATE your_table SET column1 = CASE WHEN your_condition THEN new_value1 ELSE column1 END, column2 = CASE WHEN your_condition THEN new_value2 ELSE column2 END WHERE your_condition;

  5. "SQL Update multiple rows with VALUES from another table"

    • Update multiple rows with values from another table when a condition exists.
    UPDATE table1 SET column1 = ( SELECT column1 FROM table2 WHERE table2.id = table1.id ), column2 = ( SELECT column2 FROM table2 WHERE table2.id = table1.id ) WHERE your_condition;

  6. "SQL Update using EXISTS condition for multiple rows"

    • Update multiple rows using the EXISTS condition with another table.
    UPDATE table1 SET column1 = new_value1, column2 = new_value2 WHERE EXISTS ( SELECT 1 FROM table2 WHERE table2.id = table1.id AND your_condition );

  7. "SQL Update based on multiple conditions"

    • Update multiple rows based on multiple conditions.
    UPDATE your_table SET column1 = new_value1, column2 = new_value2 WHERE condition1 AND condition2;

  8. "SQL Update with JOIN and WHERE condition"

    • Update multiple rows using a JOIN and additional WHERE conditions.
    UPDATE table1 SET column1 = table2.column1, column2 = table2.column2 FROM table1 JOIN table2 ON table1.id = table2.id WHERE your_condition1 AND your_condition2;

  9. "SQL Update based on aggregate condition"

    • Update multiple rows based on an aggregate condition.
    UPDATE your_table SET column1 = new_value1 WHERE id IN ( SELECT id FROM your_table GROUP BY id HAVING COUNT(*) > 1 );

  10. "SQL Update with subquery and TOP for multiple rows"

    • Update multiple rows using a subquery with the TOP clause.
    UPDATE table1 SET column1 = ( SELECT TOP 1 column1 FROM table2 WHERE table2.id = table1.id ) WHERE your_condition;

More Tags

contentsize xpath-1.0 viewcontroller spring-scheduled extrafont streamwriter react-async android-gui relationship android-pay

More Programming Questions

More Transportation Calculators

More Date and Time Calculators

More Internet Calculators

More Bio laboratory Calculators