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.

learn more… | top users | synonyms (1)

15
votes
2answers
10k views

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 = ...
12
votes
1answer
4k views

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 ...
12
votes
3answers
8k views

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 ...
8
votes
5answers
87k views

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 ...
8
votes
1answer
14k views

Trigger to UPDATE after UPDATE?

I want to make a trigger to record the time of any update as CREATE TRIGGER col_update AFTER UPDATE ON col FOR EACH ROW BEGIN UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id END The ...
7
votes
2answers
14k views

Update many rows in a table with a single statement?

What is the easiest way to update many rows in a table? I have a csv file that looks like this: |primary_key |value| | 1 | xyz| | 2 | abc| | 3 | def| ... Rows with ...
7
votes
1answer
2k views

Direct the OUTPUT of an UPDATE statement to a local variable

I would like to do this : DECLARE @Id INT; UPDATE Logins SET SomeField = 'some value' OUTPUT @Id = Id WHERE EmailAddress = @EmailAddress -- this is a parameter of the sproc Is this even ...
7
votes
2answers
6k views

How can I update a json element in postgresql json datatype

I can't figure how can I update an element in a postgresql 9.3 datatype. My exemple the db CREATE TABLE "user" ( id uuid NOT NULL, password character varying(255), profiles json, gender ...
6
votes
2answers
388 views

Is this update, select combination query thread safe?

I have a table called seats, which has a schema like this id,taken For each user, I take a random untaken id and assign to that user, here for simplicity say, I make taken =1. The query I am ...
6
votes
1answer
201 views

.WRITE clause performance optimization

I have found that you can do high performance updates on varchar(max),nvarchar(max),varbinary(max) columns using the .WRITE clause(example here and here). Unfortunately, there is nothing mentioned ...
6
votes
1answer
11k views

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 ...
5
votes
3answers
11k views

Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn. First, I do this: select max(ora_rowscn) from ...
5
votes
3answers
7k views

Cascade primary key update to all referencing foreign keys

Is it possible to update a primary key column value with cascading the update among all the foreign keys referencing it ? # EDIT 1: When I run followinq query select * from sys.foreign_keys where ...
5
votes
1answer
6k views

UPDATE table based on the same table

I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same ...
5
votes
2answers
1k views

Adding a sequence number to existing rows

I have a table with an OrderSequence column. The schema looks roughly like this: id Name OrderSequence I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the ...
5
votes
1answer
13k views

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` ...
5
votes
6answers
2k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
4
votes
2answers
11k views

Updating Multiple Rows with Data from Column A into Column B?

I have about 60,000 rows that I'm needing to update the information from column_a to column_b. For example, 60,000 customers need their arrival_time to match their departure_time. I understand that ...
4
votes
2answers
899 views

Insert RECORD into a table in a trigger function

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table ...
4
votes
1answer
4k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
4
votes
1answer
790 views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
4
votes
1answer
84 views

MySQL 1175 Error While Using Primary Keys

What would cause the error 1175 that starts out with: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.... The problem I have is that I did include ...
4
votes
1answer
10k views

Oracle: Non key-preserved table should be

I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
4
votes
2answers
394 views

fast bulk incrementing in MySQL

I have one big table foobar describing a many-to-many-relation and containing millions of foo's, millions of bar's and every bar having several hundereds of foo's -> billions of rows. CREATE TABLE ...
4
votes
2answers
307 views

ON DUPLICATE KEY UPDATE faster than UPDATE

I have a table with about 17 million rows: mysql> describe humans_we_respect; ...
4
votes
1answer
460 views

Oracle UPDATE followed by SELECT in LinqPad

I'm trying to UPDATE values in an Oracle database table (of salary information) that has no row IDs or a primary key. I'm writing these statements in LinqPad. This syntax should work just fine in ...
3
votes
3answers
9k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
3
votes
3answers
4k views

Update field with new string made from characters in existing value

field is meta_value and current value is like 01/01/2006 i want to change it to a string 20060101 so thought i could get the value and update it with the characters in the correct order. ie UPDATE ...
3
votes
2answers
7k views

GROUP BY two columns

I want to count two columns, but need to get the results of first column in one row. SELECT country, COUNT(*) FROM table1 GROUP BY country, type This query gives me country type COUNT(*) ...
3
votes
4answers
3k views

Update column with the sum of column in previous rows in MySQL

We have a table, essentially: id, amount, end balance, account_id I now want to re-calculate the end balance values (because of possible corruption) End balance is basically "end balance of ...
3
votes
3answers
1k views

INNER JOIN Giving time out on large database

Getting time out on this script UPDATE uk_data AS ud INNER JOIN uk_pc AS up ON ud.cat10 = up.WardCode SET ud.cat8 = up.Latitude, ud.cat9 = up.Longitude; uk_pc Table ...
3
votes
3answers
47 views

Problems updating with inner join with multiple on statements

I have an orders table on a database that contains geographical information about the city and state that the order is from. This includes latitude and longitude for doing haversine calculations. I ...
3
votes
3answers
208 views

INSERT/UPDATE in same query from previous SELECT with some singularities

I'm trying to perform a INSERT|UPDATE by selecting data from another DB and this is what I have so far: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,`first`,`last`,email) SELECT ...
3
votes
2answers
4k views

Update statement is giving an invalid column name error, even though the column exists

I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement CREATE TABLE ...
3
votes
2answers
852 views

How to update ID=null values in table to incremental counter values?

On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows: ID NaturalID Comment 1 ...
3
votes
1answer
14k views

MYSQL - UPDATING multiple rows with different values in one query (for dummies)

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand. For instance, two updates ...
3
votes
2answers
933 views

postgreSQL update set only where cast is possible and ignore error?

I have 2 columns in a PostgreSQL table. The mac_address_temp column is for migration from character type to MAC-address type: mac_address | macaddr | mac_address_temp | character ...
3
votes
1answer
2k views

Update to session table slow

I have a number of websites with session tables on a web server, these are used by Joomla CMS and Symfony framework systems. With slow_query_log=0.6, the update queries for the session tables come up ...
3
votes
1answer
37 views

Update columns values using select from external database and table in MariaDB trigger

I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using ...
3
votes
1answer
373 views

How to reuse an update trigger for multiple tables in postgresql?

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table: CREATE TRIGGER ...
3
votes
1answer
744 views

See actual results of SQL UPDATE command

Is there a way to get the actual results of SQL UPDATE command (ie the table rows themselves), not just get the number of rows (other than by converting the UPDATE command to a SELECT)? I'm using ...
3
votes
1answer
56 views

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 ...
3
votes
1answer
40 views

Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?

I'm using PostgreSQL 9.3.5. Suppose I have the following table: CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point); I then insert a row, leaving pt NULL: INSERT INTO point_test ...
3
votes
1answer
8k views

Update all columns from another table

I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this: update tableA set * ...
3
votes
1answer
2k views

Update one table from another table while sorting that table based on one column

This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new ...
3
votes
2answers
9k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
3
votes
2answers
2k views

DB2 UPDATE TRIGGER

I am trying to create a trigger in a DB2 database that runs on the update of a column in one table, and then fills in another table with certain values. For example, there is a power unit table with ...
3
votes
1answer
3k views

How to re-assign two variables in a conditional UPDATE

Consider a simple update as SET @var1 = 20; SET @var2 = 26; UPDATE table1 SET col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5), col2=IF(@var2>50, @var2 := @var2-col6, @var2); ...
3
votes
1answer
994 views

A function to check if a column allows NULL

Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise? I would be something like: UPDATE mytable ...
3
votes
1answer
33 views

MySQL not updating a row (zero row(s) affected) [closed]

I cannot figure out why MySQL 5.6.25 will not update an existing row: SELECT lcl_booking.file_number_id, lcl_booking.entered_datetime, lcl_booking.modified_datetime FROM lcl_booking WHERE ...