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

3
votes
1answer
3k 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
1k 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
17k views

Use CASE to select columns in UPDATE query?

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so: SELECT CASE WHEN val = 0 THEN column_x WHEN val = 1 THEN column_y ELSE 0 END AS ...
2
votes
2answers
6k 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
3answers
8k 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 ...
2
votes
2answers
7k 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
1k 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
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
1answer
12k 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 ...
2
votes
2answers
268 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
176 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
62 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
1answer
167 views

Does a database update force index rebuild regardless of updated field?

With MySQL (InnoDB), I understand that having indexes slow down UPDATE queries. I would like to know if this happens regardless of what fields are updated. For example, if I have a table with 20 ...
2
votes
2answers
23k 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
184 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
272 views

Updating column in underlying table causes massive transaction log growth

I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry ...
2
votes
2answers
187 views

Help with tricky update statement

I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier. I have two table variables: DECLARE ...
2
votes
1answer
134 views

Update taking too long

I am updating a table with 250 million rows in SQL Server, and it's been running for 4.5 days. I'd like to use the computer it's running on for other memory-intensive work, so I'm torn about whether ...
2
votes
2answers
67 views

How do you update a large MySQL table that's on a server?

I have an application on a web server. The PHP front end for the application queries a MySQL database which has a table that contains 60 million taken domain names. My application queries this ...
2
votes
1answer
468 views

Create a new table (multiple columns) by selecting random values from an existing table

I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows): table1: id 754 800 330 4 59 My goal is to create another table2 with two columns ...
2
votes
1answer
226 views

Does MySQL do UPDATE in place on NOT NULL column?

Assume I have a table like this in MySQL: CREATE TABLE Foo ( Key INT NOT NULL , MyCol INT NOT NULL PRIMARY KEY (Key)) And I execute a statement like this on: UPDATE Foo SET MyCol = 13 WHERE Key = ...
2
votes
1answer
362 views

Apply in a CSV file to multiple databases

Recently, I have been working with a client whom has asked us to provide their dataset for use in a mobile application we are making for them. The requirements for this project include the ability to ...
2
votes
1answer
701 views

Update ranking on table

I have two tables: mysql> desc rank ; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
0answers
21 views

How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions: CREATE TABLE products ( id bigint NOT NULL, title character varying(75), description text, manufacturer_id bigint, created_at ...
2
votes
0answers
26 views

How to order by fields of joined table while selecting for update?

For selecting some rows of table Fwt_Chipkarte for update I use the following SQL statement (IBM DB2): SELECT o.Archiviert, o.Id, o.IdComm, o.Kartennummer, o.Konto, o.LetzteAenderung, ...
2
votes
0answers
39 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
2
votes
1answer
96 views

Updating large table worked before, now taking hours

BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not ...
2
votes
0answers
80 views

How to optimize update using join in postgresql

I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems ...
2
votes
1answer
37 views

Translate SQL Server update statement to MySQL

I have a development environment with MySQL and a production environment with SQL Server. This update statement works with SQL Server: UPDATE THIS SET <list_of_column_assignments> FROM ...
2
votes
0answers
250 views

How to execute a non-table-locking update operation on PostgreSQL? [closed]

Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
2
votes
0answers
291 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
2
votes
1answer
4k views

I have to update all the rows of a Column in a table. Here is my query

So My tables are People: People_name(VARCHAR 100), People_id (INT, PRIMARY KEY AUTO INCREMENT) Tracks: Track_id(INT PRIMARY KEY), People_id_references (int ,Foreign key references ...
1
vote
4answers
584 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
1
vote
3answers
172 views

How to handle update or insert situations

From time to time my stored procedures looks like create procedure handle_data @fk int ,@value varchar(10) as begin if exists (select * from my_table ...
1
vote
3answers
2k views

How to handle big data update based on excel spreadsheet

Scenario Select-based data is exported from DB to Excel spreadsheets. Some manual work is done on the excel files (filling missing data). Changed data from excel files is updated back to DB. ...
1
vote
3answers
122 views

How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

I'm working on PostgreSQL and I have a table like this called words_table: I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
1
vote
2answers
125 views

Is column order reliable in UPDATE?

Can we reply on the order of columns in an UPDATE? SET @x = 0 UPDATE table1 SET col1=IF(col1 IS NULL, NULL, @x:=@x+col1), col2=IF(col2 IS NULL, NULL, @x:=@x+col2), col3=IF(col3 IS NULL, NULL, @x:=0) ...
1
vote
2answers
135 views

T-SQL — Why do ALTER and UPDATE statement only run when separated into their own batch?

I have update and alter statements that are only running when they are put into they're own batch. Below is my shortened version. It'd probably be a good idea to skip the code and read my question ...
1
vote
2answers
502 views

Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app. I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: ...
1
vote
1answer
140 views

numbering rows consecutively for a number of tables

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to ...
1
vote
2answers
164 views

How does optimistic locking actually enforce re-read/update?

My understanding of optimistic locking is that it uses a timestamp on each record in a table to determine the "version" of the record, so that when the record is access by multiple processes at the ...
1
vote
2answers
697 views

Way to update many (>3000) records with unique data, using a character limited web form

Okay, as you can probably tell from the last part of the title, this is a non-ideal situation. I'm using a piece of industry-specific web-interface software that I guarantee no one has ever heard of, ...
1
vote
1answer
38 views

How does the table scan in an UPDATE WHERE predicated on an unindexed column work?

I was looking into a deadlock caused by two concurrent UPDATE statements: UPDATE [table] SET [column] = 0 WHERE [unindexed_column] = @id My understanding is that because the column the WHERE is ...
1
vote
3answers
266 views

Undo SQL UPDATE Azure DB

I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table. I've read around and found ApexSQL Log ...
1
vote
2answers
295 views

Install SP 3 on SQL Server 2008, where merge replication is running

I have a SQL Server 2008 and I should install Service Pack 3 for it. On my server there are several databases replicated, and mobile devices sync data via merge replication. Can problems occur, if I ...
1
vote
1answer
2k views

Why I cannot let EXPLAIN update statements?

I can imagine that it's actually more important to explain SELECT statements, but I wonder why there's no possibility to use EXPLAIN on UPDATEs/INSERTs. I may use a WHERE-statement in an UPDATE where ...
1
vote
1answer
34 views

How to update table1 field by using other table and function

I have two tables and one function: Table1 contains shop_code,batch_id shop_code| batch_id | regstriy_id 123 | 100 | 12 124 | 100 |13 125 | 100 |12 Table2 contains shop_code,shop_name ...
1
vote
1answer
63 views

Set bit in Table A if values are present in Table B

I have two tables, Containing up to ~10 million entries. TableA which has 3 columns: IDA (bigint), IDB (int) and CHK (bit). (IDA, IDB) is the primary key. TableB which has 2 columns: IDA (bigint), ...
1
vote
1answer
154 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = ...
1
vote
1answer
101 views

Create constraint for stopping mass update to a table

I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.