I'm trying to update the records in a column within database. My current codes are as below:
curs = conn.cursor()
statement='SELE开发者_如何学GoCT 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.
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'
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.
An insert
does not update values in a database, it adds records. You need to use an UPDATE
statement.
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 aWHERE
clause, etc. to manipulate the view of the data. This is the safest operation (though a badSELECT
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 yourWHERE
clause in aSELECT
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 yourWHERE
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.
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
精彩评论