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)

3
votes
1answer
3k views

Oracle 10g: Optimizing an update query that uses a subquery

I have the following query within a stored procedure, and I've been trying to improve its performance to no avail. UPDATE TBL_REPORTING REP SET (REP.ISSUER_ID, REP.ISSUER_NAME ) = ( ...
3
votes
1answer
4k 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
2answers
581 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
55 views

SQL Server: UPDATE permission for table variable

SQL Server 2012 I have a maintenance script that I wrote for some of my operators. They are members of the fixed db_datareader and db_datawriter roles. Part of the script creates a table variable, ...
3
votes
0answers
95 views

Why is my SQL Server query behaving differently on UPDATE than on SELECT?

I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great: DECLARE @RunDetailID INT = ...
3
votes
0answers
177 views

Update all rows

I want to know the most efficient manner to update every row in an extremely large Oracle table for a single column. For example: update mytable set mycolumn=null; or: update mytable set ...
3
votes
0answers
118 views

Unable to Update on SQL Server 2008 R2

This is a bit strange so bear with me please. After installing a new SQL Server 2008 R2 on a new server (Windows 2012 Standard) just like the production one, I tried to connect my web aplication ...
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
20k 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
2answers
8k 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
2answers
301 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
1answer
802 views

Msg 5145: Autogrow of database log file took 1010871 milliseconds

In the past, I had an update query that ran for 7 days. I couldn't find a solution, so I created a new database with indexes. The update query has now been running for 26 hours. I am seeing messages ...
2
votes
1answer
227 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
3answers
304 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 ...
2
votes
2answers
190 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
64 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
28k 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
67 views

Single row update using primary key affects many rows

I'm doing a very simple datetime update in a table using its primary key: update TABLE set END_DATE = '2015-10-14 00:00:00' where ID = 1165027; I received the message (1 row(s) affected) 183 ...
2
votes
2answers
110 views

INSERT… ON DUPLICATE KEY UPDATE not working as I expect

I have a table called "Example" CREATE TABLE IF NOT EXISTS `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY ...
2
votes
1answer
100 views

why update a table take so long on oracle

I have a table A which has 3800+ rows, another table B with 300+ rows. when I execute a update like this: update A set some_colum = '2' where another_column not in (select id from B) it takes 33 ...
2
votes
1answer
236 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
4k views

UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred? UPDATE table1, (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B SET table1.Freq = ...
2
votes
1answer
573 views

How to UPDATE a column by COUNT of FK from another table?

How to extend this query for the entire table, to update all ids? UPDATE table1 SET number=( SELECT COUNT(*) FROM table2 where id=1 ) WHERE id=1 id is the PRIMARY KEY in table1 and FK in table2. ...
2
votes
1answer
279 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
3answers
67 views

updating the entire column with datetime values based on other values in the row

I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic: Only add a value to ...
2
votes
1answer
19 views

Update postgres row where condition is from another table

I want to update a password field from a given value that exists from another table. user_info: -------------------------------------------- | user_login | password | ...
2
votes
1answer
96 views

Update one column based on count of records of grouped value in another column

Given a table CREATE TABLE TABLE_ITEMS ( serial_num int null, item_group int null ); INSERT INTO TABLE_ITEMS VALUES (0,1), (1,1), (2,1), (3,1), (4,1), (0,2), (1,2), (2,2), (3,2), (4,2), ...
2
votes
1answer
148 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
71 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
511 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
242 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
374 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
738 views

Update ranking on table

I have two tables: mysql> desc rank ; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
1answer
22 views

Overwrite event - data from SQL Server to MySQL

In MySQL is it possible to execute an update query with values selected from external SQL Server database? I'd like to execute every night a timed query, which overwrites some data in MySQL with data ...
2
votes
0answers
8 views

Update a column based on other values in the row [migrated]

Consider the following trials table: CREATE TABLE trials ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name_A VARCHAR(6), name_B VARCHAR(6), score_A INT, score_B INT); Abstractly, this represents a ...
2
votes
0answers
43 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
32 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
46 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
117 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
93 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
41 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
275 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
308 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
600 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
179 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 ...