Mengemaskini Baris Jadual PostgreSQL Menggunakan Subqueries
Untuk mengemas kini baris sedia ada dalam jadual PostgreSQL menggunakan nilai yang dikembalikan daripada subkueri, anda boleh menggunakan yang berikut sintaks:
UPDATE table_name SET column_name = subquery.column_name FROM (SELECT ...) AS subquery WHERE table_name.id = subquery.id;
Pertimbangkan contoh jadual tiruan anda disediakan:
CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, partner boolean ) WITH ( OIDS=FALSE );
Untuk mengemas kini lajur pelanggan, pembekal dan rakan kongsi berdasarkan nilai yang dikembalikan daripada penyata pilihan, anda boleh menggunakan sintaks berikut:
UPDATE dummy SET customer = subquery.customer, supplier = subquery.supplier, partner = subquery.partner FROM (SELECT address_id, CASE WHEN cust.addr1 IS NOT NULL THEN TRUE ELSE FALSE END AS customer, CASE WHEN suppl.addr1 IS NOT NULL THEN TRUE ELSE FALSE END AS supplier, CASE WHEN partn.addr1 IS NOT NULL THEN TRUE ELSE FALSE END AS partner FROM address AS pa LEFT OUTER JOIN cust_original AS cust ON (pa.addr1 = cust.addr1 AND pa.addr2 = cust.addr2 AND pa.city = cust.city AND pa.state = cust.state AND CAST(cust.zip AS VARCHAR(5)) = CAST(pa.zip AS VARCHAR(5))) LEFT OUTER JOIN supp_original AS suppl ON (pa.addr1 = suppl.addr1 AND pa.addr2 = suppl.addr2 AND pa.city = suppl.city AND pa.state = suppl.state AND CAST(pa.zip AS VARCHAR(5)) = CAST(CAST(suppl.zip AS VARCHAR(25)) AS VARCHAR(5))) LEFT OUTER JOIN partner_original AS partn ON (pa.addr1 = partn.addr1 AND pa.addr2 = partn.addr2 AND pa.city = partn.city AND pa.state = partn.state AND CAST(pa.zip AS VARCHAR(5)) = CAST(CAST(partn.zip AS VARCHAR(25)) AS VARCHAR(5)))) AS subquery WHERE dummy.address_id = subquery.address_id;
Pertanyaan ini melaksanakan operasi berikut:
Atas ialah kandungan terperinci Bagaimana untuk Mengemas kini Baris PostgreSQL dengan Nilai daripada Subkueri?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!