CODEWITHSUNDEEP
sqldatabasesql-update
widget
widget

Reputation: 995

How to update column records in database?

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.

Upvotes: 4

Views: 18160

Answers (5)

Miyagi Coder
Miyagi Coder

Reputation: 5532

An insert does not update values in a database, it adds records. You need to use an UPDATE statement.

Upvotes: 2

Matt Lind
Matt Lind

Reputation: 21

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.

Upvotes: 2

widget
widget

Reputation: 995

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

Upvotes: 1

David
David

Reputation: 218837

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.

Upvotes: 1

FatherStorm
FatherStorm

Reputation: 7183

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'

Upvotes: 3

Related Questions