Pregunta Campos de actualización de SQL de una tabla de campos de otra


Tengo dos mesas:

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

A siempre será un subconjunto de B (es decir, todas las columnas de A también están en B)

Quiero actualizar un registro con un específico ID en B con sus datos de A para todas las columnas de A. Esta ID existe tanto en A y B.

Hay un UPDATE sintaxis o cualquier otra forma de hacerlo sin especificar los nombres de las columnas, solo diciendo "establecer todas las columnas de A"?

Estoy usando PostgreSQL, por lo que también se acepta un comando específico no estándar (sin embargo, no es el preferido).


75
2018-05-04 08:27


origen


Respuestas:


Puedes usar el no estándar DE cláusula.

UPDATE b
SET column1 = a.column1,
  column2 = a.column2,
  column3 = a.column3
FROM a
WHERE a.id = b.id
AND b.id = 1

157
2018-05-04 15:41



La pregunta es vieja, pero sentía que aún no se había dado la mejor respuesta. 

¿Hay una sintaxis ACTUALIZADA? sin especificar los nombres de las columnas?

Solución general con SQL dinámico

No necesita conocer ningún nombre de columna, excepto algunas columnas únicas para unirse (id en el ejemplo). Funciona de manera confiable para cualquier posible caso de esquina que se me ocurra.

Esto es específico de PostgreSQL. Estoy construyendo código dinámico basado en el information_schema, en particular, la mesa information_schema.columns, que se define en ANSI SQL y la mayoría de los RDBMS modernos (excepto para Oracle) lo admiten. Pero una DO declaración con PL / pgSQL El código que ejecuta SQL dinámico es una sintaxis de PostgreSQL totalmente no estándar.

DO
$do$
BEGIN

EXECUTE (
SELECT
'UPDATE b
 SET   (' || string_agg(quote_ident(column_name), ',') || ')
     = (' || string_agg('a.' || quote_ident(column_name), ',') || ')
 FROM   a
 WHERE  b.id = 123
 AND    a.id = b.id'
FROM   information_schema.columns
WHERE  table_name   = 'a'       -- table name, case sensitive
AND    table_schema = 'public'  -- schema name, case sensitive
AND    column_name <> 'id'      -- all columns except id
);

END
$do$;

Suponiendo una columna coincidente en b para cada columna en a, pero no al revés. b puede tener columnas adicionales

WHERE b.id = 123 es opcional, para actualizar solo una fila seleccionada.

SQL Fiddle.

Respuestas relacionadas con más explicaciones:

Soluciones parciales con SQL simple

Con la lista de columnas compartidas

Aún necesita saber la lista de nombres de columna que comparten ambas tablas. Con un atajo de sintaxis para actualizar varias columnas, más corto que otras respuestas sugeridas hasta ahora en cualquier caso.

UPDATE b
SET   (  column1,   column2,   column3)
    = (a.column1, a.column2, a.column3)
FROM   a
WHERE  b.id = 123    -- optional, to update only selected row
AND    a.id = b.id;

SQL Fiddle.

Esta sintaxis se introdujo con Postgres 8.2 en diciembre de 2006, mucho antes de que se formulara la pregunta.
Más detalles en el manual y esta respuesta relacionada en dba.SE:

Con la lista de columnas en B

Si todas las columnas de A están definidos NOT NULL (pero no necesariamente B),
y tú saber los nombres de las columnas de B (pero no necesariamente A)

UPDATE b
SET   (column1, column2, column3, column4)
    = (COALESCE(ab.column1, b.column1)
     , COALESCE(ab.column2, b.column2)
     , COALESCE(ab.column3, b.column3)
     , COALESCE(ab.column4, b.column4)
      )
FROM (
   SELECT *
   FROM   a
   NATURAL LEFT JOIN  b -- append missing columns
   WHERE  b.id IS NULL  -- only if anything actually changes
   AND    a.id = 123    -- optional, to update only selected row
   ) ab
WHERE b.id = ab.id;

los NATURAL LEFT JOIN se une a una fila de b donde todas las columnas del mismo nombre tienen los mismos valores. No necesitamos una actualización en este caso (nada cambia) y podemos eliminar esas filas al inicio del proceso (WHERE b.id IS NULL)
Todavía tenemos que encontrar una fila coincidente, por lo que b.id = ab.id en la consulta externa.

SQL Fiddle.

Esto es SQL estándar excepto por el FROM cláusula.
Funciona sin importar cuál de las columnas está realmente presente en A, pero la consulta no puede distinguir entre valores NULL reales y columnas faltantes en A, entonces solo es confiable si todas las columnas A están definidos NOT NULL.

Hay múltiples variaciones posibles, dependiendo de lo que saber sobre ambas tablas.


30
2018-04-25 03:46



He estado trabajando con la base de datos IBM DB2 durante más de una década y ahora estoy intentando aprender PostgreSQL.

Funciona en PostgreSQL 9.3.4, pero no funciona en DB2 10.5:

UPDATE B SET
     COLUMN1 = A.COLUMN1,
     COLUMN2 = A.COLUMN2,
     COLUMN3 = A.COLUMN3
FROM A
WHERE A.ID = B.ID

Nota: El problema principal es causa FROM que no es compatible con DB2 y tampoco con ANSI SQL.

Funciona en DB2 10.5, pero NO funciona en PostgreSQL 9.3.4:

UPDATE B SET
    (COLUMN1, COLUMN2, COLUMN3) =
               (SELECT COLUMN1, COLUMN2, COLUMN3 FROM A WHERE ID = B.ID)

¡FINALMENTE! Funciona tanto en PostgreSQL 9.3.4 como en DB2 10.5:

UPDATE B SET
     COLUMN1 = (SELECT COLUMN1 FROM A WHERE ID = B.ID),
     COLUMN2 = (SELECT COLUMN2 FROM A WHERE ID = B.ID),
     COLUMN3 = (SELECT COLUMN3 FROM A WHERE ID = B.ID)

13
2017-11-26 12:41



No necesariamente lo que preguntaste, pero tal vez usar la herencia postgres podría ayudar.

CREATE TABLE A (
    ID            int,
    column1       text,
    column2       text,
    column3       text
);

CREATE TABLE B (
    column4       text
) INHERITS (A);

Esto evita la necesidad de actualizar B.

Pero asegúrese de leer todo el detalles.

De lo contrario, lo que pides no se considera una buena práctica: cosas dinámicas como las vistas con SELECT * ... están desalentados (ya que tal conveniencia leve podría romper más cosas que ayudar a las cosas), y lo que pida sería equivalente para el UPDATE ... SET mando.


5
2018-05-04 08:48



Esta es una gran ayuda. El código

UPDATE tbl_b b
SET   (  column1,   column2,   column3)
    = (a.column1, a.column2, a.column3)
FROM   tbl_a a
WHERE  b.id = 1
AND    a.id = b.id;

funciona perfectamente.

notó que necesita un corchete "" en

From "tbl_a" a

para hacer que funcione


5
2018-05-16 18:56



puedes construir y ejecutar sql dinámico para hacer esto, pero realmente no es ideal


1
2018-05-04 08:29



Intenta seguir

Update A a, B b, SET a.column1=b.column1 where b.id=1

EDITADO: - Actualiza más de una columna

Update A a, B b, SET a.column1=b.column1, a.column2=b.column2 where b.id=1

-4
2018-05-04 08:33