如何更新数据库中的列记录？

发布于 2024-10-21 18:31:15 字数 297 浏览 1 评论 0原文

我正在尝试更新数据库中的列中的记录。我当前的代码如下：

curs = conn.cursor()
statement='SELECT column FROM table'
curs.execute(statement)
curs.execute("INSERT INTO table VALUES (4, 'Five')")

根据我的理解，该列的第 4 行应更新为“五”。我运行后，没有错误，但也没有更新。我的代码一定有问题，或者我遗漏了一些重要的东西。如果我想更新整个列的记录怎么办？预先感谢您的任何澄清。

原文

I'm trying to update the records in a column within database. My current codes are as below:

curs = conn.cursor()
statement='SELECT column FROM table'
curs.execute(statement)
curs.execute("INSERT INTO table VALUES (4, 'Five')")

In my understanding, the 4th row of the column should be updated to 'Five'. After I ran it, there is no error, but no update neither. There must be something wrong in my codes, or I'm missing something important.
What if I want to update the whole column's records? Thanks in advance for any clarification.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦回梦里 2024-10-28 18:31:15

update `table_name` set `column_name` ='value'

尽管您想非常确定您正在尝试将所有行更新为新列值，否则您需要添加

where `unique_key` ='unique_value'

update `table_name` set `column_name` ='value'

although you want to make VERY certain you are trying to update ALL of the rows to that new column values, otherwise you need to add

where `unique_key` ='unique_value'

回复收藏 0 原文

裸钻 2024-10-28 18:31:15

根据您使用的 SQL 版本（MSSQL、Oracle、MySQL），您将需要不同的语法。看起来您正在使用 MSSQL，因此您需要从以下内容开始：

UPDATE table SET column = "Five"

但是您不能只说将第 4 行设置为 X 值，SQL 不会像 Excel 电子表格那样保留设置的行号。您需要添加一个 int 列并将其命名为 PK_tablename 之类的名称，并将其设置为该表的主键。然后你可以编写这样的语句，它总是会更新正确的行：

UPDATE table SET column = "Five" WHERE PK_tablename = 4

我建议阅读 SQL 帮助中的主键。

Depending on the version of SQL you're using MSSQL, Oracle, MySQL you'll need different syntax. Looks like you're using MSSQL so you'll want to start with this:

UPDATE table SET column = "Five"

But you can't just say set row 4 to X value, SQL doesn't keep a set row number like an Excel spreadsheet. You'll want to add an int column and call it something like PK_tablename and set it to be the primary key for that table. Then you can write a statement like this and it will always update the correct row:

UPDATE table SET column = "Five" WHERE PK_tablename = 4

I would suggest reading up on Primary Keys in your SQL help.

回复收藏 0 原文

万水千山粽是情ミ 2024-10-28 18:31:15

插入不会更新数据库中的值，而是添加记录。您需要使用 UPDATE 语句。

回复收藏 0 原文

回忆那么伤 2024-10-28 18:31:15

您使用的是 INSERT 语句，而不是 UPDATE 语句。正如其他人所说，这将插入一条记录而不是更新一条记录。

简单的 SQL 查询本质上有 4 个基本操作：

SELECT：使用它来查看数据。 JOIN 表、设置WHERE 子句等来操作数据的视图。这是最安全的操作（尽管错误的SELECT 可能会降低服务器的性能）。
INSERT：这将在表中插入一行。它相当安全，因为它不会更改任何现有数据。
更新：这将更新表中的或_more_行。这里主要要担心的是 WHERE 子句。如果您不添加，您将更新表中的每一行。通常，您需要首先测试 SELECT 语句中的 WHERE 子句，以绝对确保您只更新您想要更新的行。
删除：这自然是最危险的。同样，如果没有 WHERE 子句，它将删除表中的所有行。测试您的 WHERE 子句以确保您仅删除您想要删除的行。

从 INSERT 语句的外观来看，您似乎正在尝试根据键（数字 4）更新行。由于没有错误，它似乎实际上并不是关键。如果它是表的键，它将返回一个错误，指出您无法插入具有重复键的行。

似乎（自然地，基于问题中的有限信息）您想要的是以下内容：

UPDATE table SET column = 'five' WHERE id = 4

可以找到更多信息此处，以及许多其他地方。

You're using an INSERT statement, not an UPDATE statement. As others have stated, this will insert a record rather than update one.

There are essentially 4 basic operations for simple SQL queries:

SELECT: Use this to view data. JOIN tables, set a WHERE clause, etc. to manipulate the view of the data. This is the safest operation (though a bad SELECT can bring a server's performance to its knees).
INSERT: This will insert a row into the table. It's fairly safe, as it doesn't change any existing data.
UPDATE: This will update one or _more_ rows in the table. The main thing to worry about here is the WHERE clause. If you don't include one, you will update every row in the table. Generally you want to test your WHERE clause in a SELECT statement first to make absolutely sure you're updating only the rows you want to update.
DELETE: This is, naturally, the most dangerous. Again, without a WHERE clause it will delete all rows in the table. Test your WHERE clause to make sure you're deleting only the rows you want to delete.

From the looks of your INSERT statement, it seems that you're trying to update a row based on a key (the number 4). Based on the lack of error, it doesn't seem to actually be a key. If it was the table's key, it would have returned an error saying that you can't insert a row with a duplicate key.

It seems (based on the limited information in the question, naturally) that what you want is something along the lines of:

UPDATE table SET column = 'five' WHERE id = 4

More information can be found here, among many other places.

回复收藏 0 原文

想你只要分分秒秒 2024-10-28 18:31:15

我使用下面的代码解决了我的问题：

conn = psycopg2.connect(conn_string)
curs = conn.cursor()
statement='UPDATE table SET column = false'
curs.execute(statement)
conn.commit()
conn.close()

感谢大家的帮助

I solved my problem using the codes below:

conn = psycopg2.connect(conn_string)
curs = conn.cursor()
statement='UPDATE table SET column = false'
curs.execute(statement)
conn.commit()
conn.close()

Tks for you all's help

回复收藏 0 原文

~没有更多了~