Pertanyaan Bidang pembaruan SQL dari satu tabel dari bidang lain


Saya memiliki dua tabel:

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

A akan selalu menjadi bagian dari B (berarti semua kolom A juga masuk B).

Saya ingin memperbarui catatan dengan spesifik ID di B dengan datanya dari A untuk semua kolom A. Ini ID ada keduanya di A dan B.

Apakah ada sebuah UPDATE sintaks atau cara lain untuk melakukannya tanpa menentukan nama kolom, hanya mengatakan "mengatur semua kolom A"?

Saya menggunakan PostgreSQL, jadi perintah non-standar tertentu juga diterima (namun, tidak disukai).


75
2018-05-04 08:27


asal


Jawaban:


Anda bisa menggunakan yang tidak standar DARI ayat.

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



Pertanyaannya sudah tua tetapi saya merasa jawaban terbaik belum diberikan. 

Apakah ada sintaks UPDATE ... tanpa menentukan nama kolom?

Solusi umum dengan SQL dinamis

Anda tidak perlu mengetahui nama kolom kecuali untuk beberapa kolom unik untuk bergabung di (id dalam contoh). Bekerja dengan andal untuk setiap kasus sudut yang bisa saya pikirkan.

Ini khusus untuk PostgreSQL. Saya membangun kode dinamis berdasarkan pada information_schema, khususnya meja information_schema.columns, yang didefinisikan dalam ANSI SQL dan RDBMS paling modern (kecuali untuk Oracle) mendukungnya. Tapi a DO pernyataan dengan PL / pgSQL kode mengeksekusi SQL dinamis benar-benar non-standar sintaks PostgreSQL.

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$;

Dengan asumsi kolom yang cocok dalam b untuk setiap kolom di a, tapi tidak sebaliknya. b dapat memiliki kolom tambahan.

WHERE b.id = 123 bersifat opsional, untuk memperbarui hanya baris yang dipilih.

SQL Fiddle.

Jawaban terkait dengan lebih banyak penjelasan:

Solusi parsial dengan SQL biasa

Dengan daftar kolom bersama

Anda masih perlu mengetahui daftar nama kolom yang dibagikan oleh kedua tabel. Dengan shortcut sintaks untuk memperbarui beberapa kolom - lebih pendek dari apa yang disarankan oleh jawaban lain sejauh ini.

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.

Sintaks ini diperkenalkan dengan Postgres 8.2 pada Des. 2006, jauh sebelum pertanyaan diajukan.
Keterangan lebih lanjut di manual dan jawaban terkait ini pada dba.SE:

Dengan daftar kolom di B

Jika semua kolom A didefinisikan NOT NULL (tetapi belum tentu B),
dan kamu tahu nama kolom dari B (tetapi belum tentu 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;

Itu NATURAL LEFT JOIN bergabung dengan satu baris dari b di mana semua kolom dengan nama yang sama memiliki nilai yang sama. Kami tidak memerlukan pembaruan dalam hal ini (tidak ada perubahan) dan dapat menghilangkan baris-baris tersebut di awal proses (WHERE b.id IS NULL).
Kita masih perlu mencari baris yang cocok, jadi b.id = ab.id dalam permintaan luar.

SQL Fiddle.

Ini adalah standar SQL kecuali untuk FROM ayat.
Ia bekerja terlepas dari kolom mana yang benar-benar ada A, tetapi kueri tidak dapat membedakan antara nilai NULL aktual dan kolom yang hilang di A, sehingga hanya dapat diandalkan jika semua kolom dalam A didefinisikan NOT NULL.

Ada beberapa variasi yang mungkin, tergantung pada apa yang Anda tahu tentang kedua tabel.


30
2018-04-25 03:46



Saya telah bekerja dengan database DB2 IBM selama lebih dari satu dekade dan sekarang mencoba untuk belajar PostgreSQL.

Ia bekerja pada PostgreSQL 9.3.4, tetapi tidak berfungsi pada DB2 10.5:

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

Catatan: Masalah utama adalah DARI penyebab yang tidak didukung di DB2 dan juga tidak dalam ANSI SQL.

Ia bekerja pada DB2 10.5, tetapi TIDAK bekerja pada PostgreSQL 9.3.4:

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

AKHIRNYA! Ini bekerja pada kedua PostgreSQL 9.3.4 dan 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



Belum tentu apa yang Anda minta, tetapi mungkin menggunakan warisan postgres dapat membantu?

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

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

Ini menghindari kebutuhan untuk memperbarui B.

Tetapi pastikan untuk membaca semua rincian.

Jika tidak, apa yang Anda minta tidak dianggap praktik yang baik - hal-hal dinamis seperti pandangan dengan SELECT * ... tidak disarankan (karena sedikit kemudahan dapat memecahkan lebih banyak hal daripada membantu), dan apa yang Anda minta akan setara dengan UPDATE ... SET perintah.


5
2018-05-04 08:48



Ini sangat membantu. Kode

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;

bekerja dengan sempurna.

perhatikan bahwa Anda memerlukan braket "" di

From "tbl_a" a

untuk membuatnya bekerja.


5
2018-05-16 18:56



Anda dapat membangun dan mengeksekusi sql dinamis untuk melakukan ini, tetapi itu benar-benar tidak ideal


1
2018-05-04 08:29



Coba Ikuti

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

DIEDIT: - Perbarui lebih dari satu kolom

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

-4
2018-05-04 08:33