Sql update fields of one table from fields of another one

sql

dynamic-sql

postgresql

database-queries

byAnton Shumikhin·Oct 14, 2024

Are you eager to synchronize fields across tables in SQL? Here's your down-to-earth solution:

UPDATE a
SET a.field = b.field
FROM TableA a
JOIN TableB b ON a.id = b.id
WHERE a.field <> b.field OR a.field IS NULL;

This query works like a charm—syncing the field in TableA from TableB if their id matches and the field either differs or is null in TableA.

Untangling dynamic field updates in PostgreSQL

For PostgreSQL enthusiasts, updating fields dynamically can be a piece of cake with advanced SQL techniques. Here, we can weave dynamic SQL with information_schema to form a query that adapts to the columns present.

DO $$
DECLARE
  column_record RECORD;
  query TEXT := 'UPDATE TableB SET ';
BEGIN
  FOR column_record IN SELECT column_name FROM information_schema.columns
                       WHERE table_schema = 'public' AND table_name = 'TableA'
  LOOP
    query := query || format('%I = (SELECT %I FROM TableA WHERE id = TableB.id), ', 
             column_record.column_name, column_record.column_name);
    -- LOOP DE LOOP... and pull, and your shoes are looking cool 👟
  END LOOP;

  query := rtrim(query, ', ') || ' WHERE id IN (SELECT id FROM TableA);';
  EXECUTE query;
END $$;

Now, all the columns in TableA get updated in TableB automagically, without performing a roll-call for each one! Use COALESCE in PostgreSQL to update only the non-empty guest list. Mind your manners—ensure TableA is a subset of TableB to avoid spoiling the party with mismatched guests!

Decoding best practices and seeking alternatives

Tapping into PostgreSQL's Superpowers

Ever since PostgreSQL donned its 8.2 cape, updating with JOINs has become more streamlined. Try running this faster-than-light command:

UPDATE TableB b
SET (column1, column2) = (a.column1, a.column2)
FROM TableA a
WHERE b.id = a.id;

Stuck in a multi-column labyrinth? Let subqueries navigate you:

UPDATE TableB
SET column1 = a.sub_column1,
    column2 = a.sub_column2
FROM (SELECT id, column1 AS sub_column1, column2 AS sub_column2 FROM TableA) a
WHERE TableB.id = a.id;

Keep calm and bracket special character table names—avoid syntax eruptions! Ponder on SQL platform compatibility—every SQL dialect speaks its own lingo.

Bolting Dynamic SQL down to Earth

Dynamic SQL and views whisper the song of freedom, but freedom comes with responsibilities. Be explicit with column specification to dodge unforeseen loopholes, especially in high-stakes production environments. Pause, ponder, and parse the implications of non-standard commands.

Keep Feeding your SQL Brain

Don't just feast on quick solutions—nourish your SQL knowledge for building robust and efficient queries.

Harnessing practical nuances and thwarting potential pitfalls

Nullifying non-null and default value problems

When your target column boasts a default value or carries a NOT NULL tag, safeguard your update query:

UPDATE TableB b
SET column1 = COALESCE(a.column1, b.column1),
    column2 = COALESCE(a.column2, b.column2)
FROM TableA a
WHERE b.id = a.id;

Familial Traits in PostgreSQL

Groove in with PostgreSQL's inheritance feature to create a new table for updates:

CREATE TABLE TableC (LIKE TableB INCLUDING ALL) INHERITS (TableB);

UPDATE TableC c
SET field = a.field
FROM TableA a
WHERE c.id = a.id;

Setting Triggers on the Go

Consider engraving a trigger to autoupdate when TableA data changes. Essentially, this turns your database into a "set it and forget it" slow cooker:

CREATE OR REPLACE FUNCTION sync_tables()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE TableB
  SET column1 = NEW.column1,
      column2 = NEW.column2
  WHERE id = NEW.id;
  -- Cool, I'm updating those fields like I update my software — when someone reminds me to. 😅
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_trigger
AFTER UPDATE ON TableA
FOR EACH ROW EXECUTE FUNCTION sync_tables();