Jennifer's questions

我希望 SQL 语句更新有限数量的行，而不是每一行：

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID;

我希望该语句的行为与以下语句相同：

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2
WHERE Join_Test_1.PKID = Join_Test_2.PKID;

UPDATE Join_Test_1
SET ANumber = 3
WHERE EXISTS (SELECT * FROM Join_Test_2 
              WHERE Join_Test_2.PKID = Join_Test_1.PKID);

UPDATE Join_Test_1
SET ANumber = 3
WHERE PKID IN (SELECT PKID FROM Join_Test_2);

使用该子句的语句WHERE仅更新匹配ANumber的列PKID。

该语句使用INNER JOIN更新表中的所有行。

为什么INNER JOIN不限制更新的行数？

是否可以重写带有的语句INNER JOIN以使用来JOIN限制更新的行数？

/* Expansive Example */
CREATE TABLE Join_Test_1 (PKID SERIAL, ANumber INTEGER);
CREATE TABLE Join_Test_2 (PKID SERIAL, ANumber INTEGER);

INSERT INTO Join_Test_1 (ANumber) VALUES (1), (1);      
INSERT INTO Join_Test_2 (ANumber) VALUES (2);       

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID; -- Updates 2

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;

-- 1, 3
-- 2, 3

UPDATE Join_Test_1 
SET ANumber = 1; -- Update 2

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2
WHERE Join_Test_1.PKID = Join_Test_2.PKID;

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;

-- 1, 1
-- 2, 3     

UPDATE Join_Test_1 SET ANumber = 1; -- Update 2

UPDATE Join_Test_1
SET ANumber = 3
WHERE PKID IN (SELECT PKID FROM Join_Test_2);

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;

-- 1, 1
-- 2, 3     

DROP TABLE IF EXISTS Join_Test_1;
DROP TABLE IF EXISTS Join_Test_2;

我想导入一个 Excel 文件，其中时间格式为“00:00”（分钟：秒）。数据库中的相应字段指定为整数。

我可以使用哪个函数输入值并将其转换为相应的整数表示？例如：“02:39”应该返回“159”。

我尝试使用 Google 搜索并查看各种来源，例如： https ://www.postgresql.org/docs/current/functions-datetime.html 。但我找不到所需的函数