Sql update query using joins

Update with aliases

Pretend you're an undercover agent and give your tables some cool aliases. If you need more guidance, here's a detailed example with aliases im for item_master and s for source:

UPDATE im
SET im.column_to_update = s.column_with_new_value
FROM source_table AS s
INNER JOIN item_master AS im
ON im.matching_column = s.matching_column
WHERE im.condition_column = 'specific_value'
AND s.filter_column LIKE 'pattern%';


Aliases are your friend, use them to keep things readable. Plus, it makes you feel like a secret agent, doesn't it?😎

Mastering SQL UPDATEs: Your way to becoming the SQL Batman!

The Convert: From SELECT to UPDATE

Being the SQL Batman starts with a test drive. Start off with a SELECT query and once you've ensured that you've got the right data, jump into your Batmobile, ahem, I mean, convert your SELECT into your UPDATE:

-- SELECT for testing: The Batmobile is on standby
SELECT im.column_to_update, s.column_with_new_value
FROM item_master AS im
INNER JOIN source_table AS s ON im.matching_column = s.matching_column;

-- Convert to UPDATE: Time to hit the streets of Gotham!
UPDATE im
SET im.column_to_update = s.column_with_new_value
FROM item_master AS im
INNER JOIN source_table AS s ON im.matching_column = s.matching_column;


The Calm before the Storm: Pre-update Checks

Just like our caped crusader does a vigilant surveillance of Gotham, perform a check with SELECT before swooping in with UPDATE to ensure only the intended rows are affected:

-- Batman's surveillance run
SELECT alias_target.id, alias_target.column_to_update, alias_source.column_with_new_value
FROM source_table AS alias_source
JOIN target_table AS alias_target ON alias_target.matching_column = alias_source.matching_column
WHERE alias_target.condition_column = 'some_value';


The Safehouse: Enclosing Updates in Transactions

Being the SQL Batman means taking care of your Gotham (your database). Enclose your UPDATE in a transaction for safer execution. You get a superpower: rollback in case of errors!

BEGIN TRANSACTION;

-- Batman's action plan goes here

-- Choose your ending: Happy or tragic? 
-- COMMIT; to persist changes or ROLLBACK; to undo all the changes. 
-- or ask Alfred for advice.
-- COMMIT;
-- ROLLBACK;


The Batwing: Taking CTEs for a spin in complex scenarios

Like the Batwing allows Batman to navigate the skies, Common Table Expressions (CTEs) help navigate complex UPDATE scenarios. CTEs separate the JOIN logic from UPDATE, making your SQL Gotham cleaner and easier to manoeuvre:

-- Batwing in action
WITH CTE_JoinedData AS (
  SELECT im.id, s.column_with_new_value
  FROM item_master AS im
  INNER JOIN source_table AS s ON im.matching_column = s.matching_column
  WHERE s.condition_column LIKE 'pattern%'
)
-- Launching the Batmobile with CTE data
UPDATE item_master
SET item_master.column_to_update = CTE_JoinedData.column_with_new_value
FROM item_master
INNER JOIN CTE_JoinedData ON item_master.id = CTE_JoinedData.id;


Janus: Handling two-faced updates with multiple joins

When dealing with multiple tables akin to dealing with Batman's nemesis Janus, or Two-Face, employ the same join strategy but add more JOIN clauses. Remember, the coin flip decides who survives in Gotham City, so clearly define your JOIN conditions:

UPDATE im
SET im.column_to_update = s.column_with_new_value,
    im.other_column = ot.column_with_other_value
FROM item_master AS im
INNER JOIN source_table AS s ON im.matching_column = s.matching_column
INNER JOIN other_table AS ot ON im.another_matching_column = ot.matching_column
WHERE im.condition_column = 'specific_value';
-- Ah! The classic Two-face strategy mayhem under control!


Don't forget to know your villains and the sidekicks (columns you want to update)!