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

10
votes
3answers
4k 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
2answers
6k 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 = ...
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
1answer
9k 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 ...
5
votes
5answers
54k 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 ...
5
votes
3answers
7k 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
2answers
10k 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 ...
5
votes
2answers
162 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
6answers
1k 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 ...
5
votes
2answers
135 views
+50

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 ...
5
votes
1answer
99 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 ...
5
votes
1answer
7k 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 ...
4
votes
2answers
7k 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
1answer
2k 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 ...
4
votes
1answer
213 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
2answers
311 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
124 views

ON DUPLICATE KEY UPDATE faster than UPDATE

I have a table with about 17 million rows: mysql> describe humans_we_respect; ...
4
votes
2answers
2k 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 ...
4
votes
1answer
359 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
1answer
709 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 ...
3
votes
3answers
2k 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
4answers
1k 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
2answers
202 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
2answers
436 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 ...
3
votes
1answer
6k 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` ...
3
votes
2answers
722 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
1k 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 ...
3
votes
1answer
362 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
34 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
4k 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 ...
3
votes
1answer
2k 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
616 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
2answers
7k 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
461 views

Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?

In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records: SELECT COUNT(id) shareLikeNo FROM ...
3
votes
1answer
2k views

update statement with self join

I needed to copy information from one row of a table to another. I noticed I could do the following: update address set col1 = a2.col1, col2 = a2.col2, .....etc from address a1, address a2 where ...
3
votes
1answer
856 views

Update rank on a large table

I have a mysql table that has now 12 million records and I'm trying to update a column (rank) with the following UPDATE code: SET @r=0; UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY ...
2
votes
2answers
3k views

After update triggers that deal with multi-row updates

Currently working on an database Audit project based on triggers that are fired on update on specific tables. The triggers write the changes into a table; information written are: table name, updated ...
2
votes
2answers
5k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor ...
2
votes
3answers
913 views

Problem with nested UPDATE queries

I cannot make an update query work. As a first step, the following one works correctly: UPDATE Tab1 SET Tab1.a = '3' WHERE Tab1.id IN ( 123, 456 ); where id is the primary key of Tab1. ...
2
votes
3answers
977 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 ...
2
votes
2answers
5k 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(*) ...
2
votes
1answer
2k views

SQL Select WHERE row updated

USE [RF_WORLD] GO /****** Object: Trigger [dbo].[lastdeathtrigg] Script Date: 08/20/2012 10:52:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ...
2
votes
2answers
208 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
2
votes
2answers
113 views

Update on view over partitioned tables updating all indexes

We have a table, which is partitioned through a date field into separate years. There is a view over all of these tables (Call) Schema is as follows: CREATE TABLE [dbo].[Call_2015]( [calID] ...
2
votes
1answer
48 views

Update only the oldest entry in a table with foreign key criteria

I'm working on a forum website. What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts". "Threads" "Posts" I'm ...
2
votes
2answers
7k views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
2
votes
1answer
1k 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 ...
2
votes
1answer
78 views

Most efficient way to do many UPDATES in Postgresql

I have to update cca 2.5 milion rows in a table with cca 20 milion rows. The updated column value is based on value of another column, but another select is required to determine it's value. The ...
2
votes
1answer
2k 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 * ...
2
votes
1answer
199 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 ...