在单个 sql 语句中更新多个表 [英] Updating multiple tables in a single sql statement

问题描述

简单的SQL 语法问题.我正在编写一个脚本来将数据从旧模式移动到新模式.执行移动时我必须关闭完整性约束，因此我的 CASCASE 不起作用.我想用这样的新值更新多个表:

Simple SQL syntax question. I'm writing a script to move data from an old schema to a new one. I have to switch off the integrity constraints when performing the move so my CASCASEs don't work. I want to update multiple tables with a new value like so:

UPDATE table1, table2 
SET table1.customer_id = 999, table2.customer_id = 999;
WHERE table1.customer_id = 3
AND table2.customer_id = 3 

什么是正确的语法?希望以上解释了我想要实现的目标?谢谢:)

what's the correct syntax though? Hopefully the above explains what I want to achieve? Thanks :).

推荐答案

试试这个:

UPDATE table1
INNER JOIN table2 USING (customer_id)
SET table1.customer_id = 999, table2.customer_id = 999
WHERE table1.customer_id = 3

从未尝试过像这样更新关键列，但这适用于其他列，因此值得一试.

Never tried it with an update to the key columns like this, but this would work for other columns, so worth a try.

这篇关于在单个 sql 语句中更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！