We’re rewarding the question askers & reputations are being recalculated! Read more.

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.

Filter by
Sorted by
Tagged with
79
votes
1answer
62k 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 ...
37
votes
2answers
46k 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 = ...
31
votes
5answers
33k views

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 ...
8
votes
1answer
13k 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 ...
5
votes
2answers
3k views

Apply the latest Service Pack and Cumulative Update

I have given an RTM version of SQL Server 2014 and I need to apply the latest SP (currently SP2) and CU (currently CU8). Do I need to apply SP1 first? Or can I straight move to SQL Server 2014 SP2 CU8 ...
2
votes
3answers
4k 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. ...
0
votes
3answers
4k views

Update values in one table based on contents of another

Software Name: Oracle SQL Developer Version 17.2.0.188 I have three tables: orders, order_details, and products. I wanted to run a query to update units_in_stock in the products table based on ...
3
votes
3answers
27k 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 bought_in_control_panel(ID,PARENT_ID,...
0
votes
1answer
1k views

Array of strings when updating a field

Unintentionally discovered that following query works in PostgreSQL: UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1; I'm passing an array of strings to field and the ...
10
votes
2answers
84k views

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

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 ...
5
votes
1answer
3k 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 ...
13
votes
1answer
46k 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 * =...
7
votes
1answer
4k views

Update by iterating table in batches faster than whole table in PostgreSQL

I have a table with approximately one million rows. It is being used in production and I ran an UPDATE which covers ~95% of those rows. After five hours I cancelled the request because it was taking ...
2
votes
3answers
1k 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 ...
9
votes
1answer
8k 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 ...
7
votes
6answers
5k 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 ...
3
votes
1answer
65 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 (...
2
votes
2answers
1k views

How to update the parent/child of all rows in the tree (ltree)?

Here are my configurations: DB type: postgresql (9.3 if that's necessary) Table name: product_sections Column names: section_id (integer) & section_path (ltree) Detail: I have one reference value:...
2
votes
1answer
167 views

UPDATE statement processing records that should be eliminated by WHERE clause

I am getting a very odd error. Consider the table below: CREATE TABLE #MyTable ( Key1 INT , Key2 INT , x SMALLINT , y INT , z INT , a FLOAT , b FLOAT , c SMALLINT , s FLOAT ) -- insert ...
1
vote
2answers
3k views

Use SUM In Update Statement

I am attempting to use SUM() in my SET statement for my UPDATE. When I try this, I get an error of Msg 157, Level 15, State 1, Line 234 An aggregate may not appear in the set list of an UPDATE ...
1
vote
1answer
4k views

mysql trigger / stored procedure - update another table row by row

I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this. this will be a little long, be patient please. First of all, I am trying ...
1
vote
2answers
4k views

How to run update query on multiple table via joins simultaneously on postgres?

In MySQL, I can do a query like this UPDATE prd_sectionshapename se inner join prd_shape s ON s.id = se.shape_id SET se.company_shape_name = 'ABC', s.name_en = 'Another name' ...
0
votes
2answers
73 views

Update a table with data from another table

I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. ...
0
votes
1answer
12k views

How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?

I have to create a xls datafeed for a website and I would like to know which tables are getting affected when I do a manual entry from CMS. If i have installed fresh database and I'm doing first ...
40
votes
4answers
269k 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 ...
32
votes
6answers
80k views

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 ...
27
votes
4answers
21k views

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 ...
9
votes
1answer
27k 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 (...
14
votes
1answer
456 views

Unique index updates and statistics row modification counters

Given the following table, unique clustered index, and statistics: CREATE TABLE dbo.Banana ( pk integer NOT NULL, c1 char(1) NOT NULL, c2 char(1) NOT NULL ); CREATE UNIQUE CLUSTERED ...
12
votes
3answers
51k 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 ...
9
votes
2answers
28k 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
2answers
6k 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 ...
5
votes
2answers
5k views

SQL Server Concurrent Inserts and Deletes

From: SQL Server 2014 Concurrent input issue Follow up question: How do we Delete and Reinsert rows in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc? Do we ...
5
votes
3answers
11k views

How to update a (very) large table without locking in MySQL

I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table. Unfortunately, whoever designed our schema didn't think things ...
2
votes
3answers
7k views

UPDATE Trigger with condition

I'm trying to trigger an update on a specific user in my user table. Basically is someone tries to change that user then I want to rollback whatever query was executed and disable the user(using a SP),...
13
votes
1answer
22k views

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 ...
5
votes
2answers
191 views

Query Plan question

I've created a varbinary hash to check changes between 2 tables. Here is the execution plan, I'm a bit stumped on the indexing, or indeed if there is a better way of writing this. https://www....
5
votes
1answer
5k 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 ...
4
votes
2answers
22k views

SQL update multiple rows in destination table with same id but different values from source table

I have two different tables with a common column called id: Table1 ---- ------- id | Date ---- ------- 1 null 1 null 2 null 2 null 2 null 2 null 3 null 4 ...
2
votes
2answers
1k views

Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen

I have a table with about 8.5m rows in it. The table is tokudb and it has the indexes described below. I'm experiencing dismal performance when trying to run update statements like the following: ...
0
votes
2answers
5k views

How to do something like UPDATE DELAYED in MySQL

I have an averages table that should keep track of an average value over time. I don't want to have a row for each value, just a single row that continuously updates the average. What I've come up ...
7
votes
1answer
31k 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
10k views

Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement?

I have this table: SELECT * FROM items id | item | position ---|-----------|---------- 1 | USB cable | 0 2 | SD card | 4 3 | Mouse | 2 4 | Keyboard | 0 5 ...
4
votes
1answer
694 views

How to atomically replace a subset of table data

In PostgreSQL 9.6 I have a table T like this category | id | data ---------+----+------ A | 1 | foo A | 2 | bar A | 3 | baz B | 4 | eh B | 5 | ...
3
votes
3answers
6k 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
2answers
11k 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
1answer
780 views

Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date): | COLUMN_NAME | TYPE | |---------------|-------------|...
3
votes
2answers
355 views

“Upsert” trigger writing mass duplicates from SSIS

I have a problem with an "upsert" trigger, being triggered by a SSIS package with fastload. (FIRE_TRIGGERS is enabled) CREATE TRIGGER [dbo].[MP_VOL_UPSERT] ON [dbo].[vwMP_VOL] INSTEAD OF ...
3
votes
1answer
5k views

Update nth element of array using a WHERE clause

I have a product table with a jsonb column named 'metadata' in a PostgreSQL 10 database. It's my first time working with documents and Postgres. jsonb values look something like this: { "name" : "...