我想要连接两个表格,并在第二个表格的指定值上更新第一个表格的值。我尝试使用其他解决方案均失败。
UPDATE customers
SET cutoffstop = cutoffstop + '432000'
FROM customers as c
JOIN bluemedia as b ON c.id = b.customerid
WHERE b.orderid = '217201807'
我想要连接两个表格,并在第二个表格的指定值上更新第一个表格的值。我尝试使用其他解决方案均失败。
UPDATE customers
SET cutoffstop = cutoffstop + '432000'
FROM customers as c
JOIN bluemedia as b ON c.id = b.customerid
WHERE b.orderid = '217201807'
一般更新语法:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
SET { column = { expression | DEFAULT } |
( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
你问题的解决方案:
UPDATE customers AS c
SET cutoffstop = cutoffstop + 432000
FROM bluemedia as b
WHERE c.id = b.customerid
AND b.orderid = '217201807'
了解更多关于UPDATE
语法的信息,请点击下面的链接:
https://www.postgresql.org/docs/current/static/sql-update.html
COUNT(*)
。 - wildplasserUPDATE customers
SET cutoffstop = cutoffstop + 432000
FROM bluemedia as b ON customers.id = b.customerid
WHERE b.orderid = '217201807'
+
来添加字符串。但如果这些是数字,你需要去掉数字周围无用的单引号。'432000'
是一个 varchar,432000
是一个整数。 - user330315EXISTS
。UPDATE customers c
SET c.cutoffstop = c.cutoffstop + '432000'
WHERE EXISTS (
SELECT 1
FROM bluemedia b
WHERE c.id = b.customerid
AND b.orderid = '217201807'
);
UPDATE customers c SET cutoffstop = cutoffstop + '432000' FROM bluemedia as b ON c.id = b.customerid WHERE b.orderid = '217201807'
- wildplasserSET cutoffstop = cutoffstop + 432000
(您不能将字符串添加到数值字段) - wildplasser