如何在带有变量表的PostgreSQL中使用UPDATE？

本文介绍了如何在带有变量表的PostgreSQL中使用UPDATE？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 示例：Table=["category1","category2","category3"]for varTable in Table: cr.execute('update varTable SET id=%s,WHERE id=%s) ........ ....如何执行此循环？推荐答案为此使用动态SQL。默认设置是将plpgsql与 EXECUTE 。 创建函数或使用 DO 语句进行临时执行。Use dynamic SQL for that. The default is to use plpgsql with EXECUTE.Create a function or use a DO statement for ad-hoc execution.CREATE OR REPLACE FUNCTION f_up(_new_id int, _old_id int) RETURNS void AS$BODY$DECLARE _tbl text[] := '{category1,category2,category3}'; t text;BEGINFOREACH t IN ARRAY _tblLOOP EXECUTE ' UPDATE ' || t || ' SET id = $1 WHERE id = $2' USING _new_id, _old_id;END LOOP;END;$BODY$ LANGUAGE plpgsql;致电：SELECT f_up(23, 45); SO有很多类似的答案。搜索 dynamic-sql ， plpgsql 和 EXECUTE 的问题c $ c>了解更多示例和解释。There are lots of similar answers on SO. Search for dynamic-sql, plpgsql and EXECUTE for more examples and explanation.如果plpgsql对您来说仍然是黑魔法，您可以使用修改数据的CTE非常有效地解决这种简单情况。需要PostgreSQL 9.1-数据修改CTE 。If plpgsql is still black magic to you, you can solve this simple case quite effectively with a data-modifying CTE. Requires PostgreSQL 9.1 - for data-modifying CTE.WITH vals AS ( SELECT 23 AS new_id, 45 AS old_id -- provide values once ) , a AS ( UPDATE category1 SET id = v.new_id FROM vals v WHERE id = v.old_id ) , b AS ( UPDATE category2 SET id = v.new_id FROM vals v WHERE id = v.old_id )UPDATE category3SET id = v.new_idFROM vals vWHERE id = v.old_id; 这篇关于如何在带有变量表的PostgreSQL中使用UPDATE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！