Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [update]

UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

UPDATE (SQL)

An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.

UPDATE (computing/software)

Piece of software that includes fixing security vulnerabilities and other bugs, and/or improving the usability or performance.

1050 questions
151
votes
1 answer

Postgres UPDATE ... LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular. I want a…
vastlysuperiorman
  • 1,685
  • 2
  • 11
  • 8
59
votes
4 answers

Does updating a row with the same value actually update the row?

I have a performance-related question. Let's say I have a user with first name Michael. Take the following query: UPDATE users SET first_name = 'Michael' WHERE users.id = 123 Will the query actually execute the update, even though it is being…
OneSneakyMofo
  • 693
  • 1
  • 5
  • 5
54
votes
2 answers

Optimizing bulk update performance in PostgreSQL

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = constant2, ... WHERE id = constid (We're just overwriting…
xyzzyrz
  • 661
  • 1
  • 6
  • 8
53
votes
6 answers

How to update 10 million+ rows in MySQL single table as Fast as possible?

Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64. I have one big table which contains around 10 millions +…
user16108
50
votes
4 answers

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT NULL, `iso` varchar(3) DEFAULT NULL, `country`…
Alireza
  • 3,676
  • 10
  • 38
  • 44
37
votes
4 answers

UPDATE performance where no data changes

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to prevent the update? For example would there be any…
Martin Brown
  • 728
  • 1
  • 5
  • 15
28
votes
3 answers

Is it possible to modify an existing trigger definition in MySQL?

I am wondering if it is possible to update a trigger definition in MySQL. For example, I have a trigger T and I want to add some new functionality to it. My assumption is that I need to drop and recreate it. What are the best practices in the…
Alex
  • 413
  • 2
  • 5
  • 7
26
votes
3 answers

How to optimization database for heavy I/O from updates (software and hardware)

The situation I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some directions on where to start improving. Here is a…
Niels Kristian
  • 971
  • 3
  • 15
  • 23
23
votes
3 answers

Update request using table alias

Executing this request: update table t1 set t1.column = 0 where t1.column2 = 1234 Getting this error: column "t1" of relation "table" does not exist This request runs fine in MySQL. Why do I get this error in PostgreSQL?
justesting
  • 333
  • 1
  • 2
  • 5
23
votes
2 answers

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate…
John Doe
  • 361
  • 2
  • 3
  • 6
22
votes
3 answers

SQL Server : How to disable trigger for an update only for your current session?

I am working on SQL Server 2008 R2. I have a table benefit which has a AFTER INSERT, UPDATE trigger named tiu_benefit. I want to write an UPDATE statement for this table to update 1 row but I dont want its trigger to fire. I know I can disable…
srh
  • 323
  • 1
  • 2
  • 5
22
votes
2 answers

Locking in Postgres for UPDATE / INSERT combination

I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once. The user needs to be able to redeem a coupon, which will insert a row into the log table and mark the coupon as used (by updating the used…
Rob Miller
  • 323
  • 1
  • 2
  • 6
21
votes
2 answers

On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` VARCHAR(250) NOT NULL, `nick` VARCHAR(32) NOT NULL, …
hjpotter92
  • 516
  • 2
  • 10
  • 24
21
votes
3 answers

What's the most efficient way to batch UPDATE queries in MySQL?

I'm writing an application that needs to flush out a large number of updates to the database for an extended period of time, and I've gotten stuck at how to optimize the query. Currently I'm using INSERT INTO ... VALUES (..), (..) ON DUPLICATE KEY…
jli
  • 313
  • 1
  • 2
  • 7
21
votes
2 answers

What's the overhead of updating all columns, even the ones that haven't changed

When it comes to updating a row, many ORM tools issue an UPDATE statement that sets every column associated to that particular entity. The advantage is that you can easily batch the update statements since the UPDATE statement is the same no matter…
Vlad Mihalcea
  • 917
  • 3
  • 9
  • 23
1
2 3
…
69 70