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)

0
votes
0answers
51 views

oracle update statement is too slow

I have oracle database 11.2.0.4 on oracle linux 6.4 I have configure huge page on this OS . After 5 months of my projects , when i want to update any tables their have over 20,000,000 records , this ...
3
votes
4answers
603 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 ...
1
vote
2answers
73 views

Updating duplicate records with different data

I have a migration specific issue. Data from a large table has been migrated into another table, but data for one destination column is missing due to certain issues (that are not specific to this ...
0
votes
1answer
4k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
6
votes
3answers
230 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
3
votes
1answer
363 views

Change SELECT DISTINCT to UPDATE DISTINCT

How can I modify my SELECT DISTINCT query to an UPDATE DISTINCT query? It is important that it only updates the distinct records, as there are multiple records associated with each ...
1
vote
1answer
63 views

Prepare MySQL for big UPDATE query on production server

Surprisingly I have not found anything relevant on this subject. What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details ...
0
votes
0answers
35 views

Copy data from one table to another

I have a table named cameraInfo and another named macTable. Both tables have a column for mac address and password. Not all the mac address in macTable are in the cameraInfo table. However, if the mac ...
11
votes
2answers
131 views

PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
0
votes
1answer
38 views

MySQL Trigger to Update a Row in a Separate Table via Joins if Only One Row is Updated

I am trying to create a trigger so that when a column in a table called bookings is updated it will update a a calculated price value in table departments. I currently have my tables set up as so ...
1
vote
1answer
29 views

Update all rows when sort/ordering value is changed for single row?

I am in the process of creating a GIS application with multiple layers and many pieces of content shapes on each layer. Currently each layer/shape is drawn in the order that it was returned from the ...
0
votes
2answers
55 views

Using a query in an update

I have a query which generates a result set (key, a, b). I want to use an update statement where, for each entry in a table with Tkey = key, set ColA = a, ColB = b. How would I do this? Right now ...
0
votes
1answer
90 views

MySQL Using a trigger to update quantity on another table if part is used

I am new to triggers but want to know how I can use one in my database. I have created a table for a maintenance log, a parts inventory, and a parts_used junction table. When maintenance is carried ...
0
votes
2answers
570 views

$addToset do not work in update if record not exists [closed]

I am trying to use following query db.TestColl.update({ "tags" : "silk" , "host" : "host.com"},{ "$set" : { "cat" : "MOVIE" , "$addToSet" : { "articleId" : { "$each" : [ "20442242"]}} , ...
3
votes
1answer
51 views

Update a column, selected by query

Just stumbled upon this stackoverflow question: http://stackoverflow.com/questions/13648898/sql-update-with-row-number. The second answer struck me as odd: UPDATE x SET x.CODE_DEST = x.New_CODE_DEST ...
6
votes
2answers
530 views

Effect of an index on update statements where update column is not in an index

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute. While tuning my database to improve performance, I keep ...
1
vote
1answer
180 views

Trigger on a Schema

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one. --Database level trigger. ...
8
votes
6answers
624 views

Switching values in a column with one update statement

You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one ...
0
votes
1answer
84 views

update statements shows update count but not updating the table

I am facing a strange issue which I have never faced before. I have a update query like below Update A Set A.Col10 = E.Col10, A.Col11 = E.Col11 FROM TableA A INNER JOIN TableB B on A.Col1 = B.Col1 ...
4
votes
1answer
74 views

Should a table be split if only a particular column gets updates?

I have a table in my MySQL 5.7 database, which has 9 columns. One column is an AUTO INCREMENT PRIMARY KEY, where as the other 6 contain some data, which isn't going to be updated in a year. Now, the ...
7
votes
2answers
293 views

8k row overflow error when updating row of size 5k

I'm trying to update a target table which has one row of size 5k to a row of size 5k also. Since it's one row it's easy to know the row actual size: select * from ...
7
votes
5answers
567 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 ...
5
votes
3answers
7k 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 ...
2
votes
1answer
204 views

How to merge 2 tables in Oracle

I have 1 big table with 25 milions of rows and i have to merge data of another table with 500,000 rows into the big table. the rows of both tables have the same columns and i have to check if the key ...
3
votes
1answer
62 views

UPDATE on column referenced in sub-query

We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following: update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, ...
3
votes
3answers
140 views

How can I optimise an Update on a large table?

I am using Oracle 11g, I have to update 7 fields on a table with 14 millions of rows, the updated columns get the new value from functions, the update looks like: update hslak set HSLAKSTS = ...
1
vote
2answers
129 views

UPDATE only if COUNT from another table is not zero

I update a table by counting the FK from another table as UPDATE table1 SET number = number + ( SELECT COUNT(*) FROM table2 WHERE table1.id=table2.id ) table1 is a big table, and I want to make ...
0
votes
1answer
40 views

Update row contents on some condition

For example, let's say I'm building a database for an auction system, in which various users can partake in both selling and bidding on different items, I'd want for an "Auction" instance to also ...
0
votes
1answer
68 views

Update if not exists statement

If I have a simple table with 2 columns and a composite primary key consisting of both columns, how do I rename one of the columns but only if it doesn't cause a primary conflict? Here's a simplified ...
10
votes
5answers
672 views

Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem. Running MS SQL Server 2012. The problem is with this UPDATE statement: UPDATE dbo.tAccts SET Ticket ...
12
votes
5answers
111k 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
1answer
2k 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 ...
1
vote
1answer
37 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
140 views

UPDATE query periodically is slower by factor of 5000x

I run a site (Magento) which have a problem with a specific UPDATE query. Under heavy load, this query runs ~200 times/minute, and takes in average 1 ms (stdev 0.2 ms). However, at intervals of 1-2 ...
1
vote
1answer
64 views

Updating multiple mysql rows where column has specific value

I have 1 CSV file with 50,000 rows of data and 2 columns. The columns are user ID's and twitter profile photos. 24525,https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png ...
0
votes
0answers
58 views

Why MySQL is still “using temporary” when update by primary key?

I have a partitioned mysql table, table size is about 100M rows: CREATE TABLE `nearby` ( `id` int(10) unsigned NOT NULL, `nearby_1` varchar(750) NOT NULL DEFAULT '', `nearby_2` varchar(750) NOT NULL ...
0
votes
1answer
99 views

Update all rows from a table with random foreign key from another table

I have four tables: (show on image below) Topic whit columns: ID primary key int CategoryID references to table Category int UserID references to table User nvarchar(128) User with ...
0
votes
1answer
22 views

MySQL Replication Query

Imagine I have three servers. x.x.x.31 - master x.x.x.32 - slave x.x.x.33 - slave If I run a simple update in master. What does the slave servers done. run the update command or copy the updated data ...
2
votes
2answers
389 views

Update column with value of another column or another column

With PostgreSQL (I'm using the version 9.1) is it possible to do mass update with a single query a column value with the value of another column, but if the other column value is null, to use the ...
2
votes
2answers
46k 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 ...
0
votes
0answers
106 views

How to create dynamic SQL using formulas stored on field - MYSQL

I have this table which contains a formula on the field calculation: CREATE TABLE IF NOT EXISTS `promo_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(150) NOT NULL, `calculation` ...
-1
votes
2answers
236 views

Is there a way that I can merge the following update queries into one?

Is it possible to merge the following update queries into a single query? UPDATE tag.ItemTagValue SET [Value] = @firstNode WHERE FkTagID = 6 and TagIndex = 1 ...
0
votes
2answers
39 views

What would cause an UPDATE statment to hang when executing from an .mdb file to SQL Server 2008 R2 over ODBC?

Is there anything that would cause an UPDATE statement INNER JOINed between a separate MS-Access linked table and an SQL Server 2008 R2 table to hang, when the values are being set on the SQL Server ...
3
votes
1answer
243 views

How to insert/update millions of rows inside a sql server table(s) in an effective way?

Often in my job I have to create a procedure inside SQL Server that will process millions of data rows, save them into a temp table (staging table) and finally save them into a table (s) in a ...
0
votes
1answer
39 views

Update an Oracle DB with a max number for the null rows

I have an Oracle db and I need to update some columns and one of them is an number that has to be some kind of an id - max number on that column. If I use something like this: update ss_chest ssc ...
2
votes
1answer
274 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
0
votes
1answer
4k 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 ...
1
vote
1answer
159 views

INSERT don't work with NOT NULL but UPDATE does, why?

I have this table definition: CREATE TABLE `media` ( `id` int(11) NOT NULL AUTO_INCREMENT, `brands_id` int(11) DEFAULT NULL, `media_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, ...
0
votes
1answer
166 views

Loop over all records in a table and edit a column in SQL Server

How can you loop over all the rows in a SQL Server table and edit a column? You know that: There are a lot of IDs skipped. The column creatie must be edit whit every time one minute. So the first ...
2
votes
2answers
261 views

MERGE command in SQL Server 2005

I have been trying to develop a MERGE in SQL Server 2005. It does not have all features yet, because I have been struggling in "how to put the merge logic WORKING AS A SET, rather than rows". ...