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
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
211 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
175 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
107 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
682 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 ...
13
votes
5answers
118k 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 ...
6
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
42 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
142 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
83 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 27753,https:...
0
votes
0answers
85 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
133 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
23 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
604 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 ...
3
votes
2answers
48k 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
151 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
237 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
41 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
356 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 database(...
0
votes
1answer
40 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
284 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` >= ...
1
vote
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
175 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
257 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
329 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". ...
0
votes
3answers
117 views

Updating rows from other rows in a huge table

I have a huge table with items item-id | name | type | extra Many of them are "old versions" of type 0. I have to update the extra field in all new items (same item-id, but type > 0) with the ...
3
votes
1answer
169 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 ...
0
votes
0answers
18 views

1175 during UPDATE in MySQL Workbench

I have this error in Mysql Workbench when I update table without using key in where clause but update query fire from my Application using connectivity of database surprisingly I have no error . ...
0
votes
1answer
29 views

Update part of timestamp field

I'm trying to fix a glitch in one of my tables. Several hundred records were given the wrong timestamp. The times are correct, but the date is wrong. I could write a little java program to fix it, ...
0
votes
1answer
59 views

SQL Server - Update using Join in Merge

Scenario : I need to update values in a table by joining it with another temp table. But I am using a Merge statement to check various scenarios and update the values in the table when the condition ...
1
vote
1answer
58 views

What is the most compact way to calculate and then Update a maximum value for each day's data?

I have a database that stores records for each bundle of material. A sample is taken from each bundle and sent out for assaying (for lead and sulfur). I receive the assay data and write the assay ...
2
votes
1answer
341 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
5
votes
4answers
536 views

How can I avoid repeating the same condition in a “when matched then update” clause?

I want to merge one table into another. I need to apply conditional logic in my WHEN MATCHED clause, which would ideally be done like this: MERGE INTO ATable AS a USING BTable AS b ON a.ID = b.ID ...
1
vote
1answer
77 views

UPDATE id table on the columns with the same name

I may be a newbie question but I don't know how to resolve this. I have a table like: name | id | value A 1286487 1286333 B 1286489 1286403 C 1286495 1286455 C ...
0
votes
1answer
203 views

Update an array element with inc mongo update

HI All I have this Data in mongo, {"articleId" : [ { "articleId" : "9514666", "articleCount" : 1 } ], "count" : NumberLong(1), ...
0
votes
1answer
18 views

PostgreSQL SQL: updating summaries

I'm trying to optimize a query like: UPDATE master SET count = (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id) Where the master table is large, so running this results in a huge ...
15
votes
3answers
432 views

Slow update on large table with subquery

With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4. UPDATE [SourceTable] SET Bad_Count= ...
1
vote
1answer
31 views

How do I uniquely update values of a text column before applying UNIQUE constraint?

I need to apply a unique constraint to a TEXT field which may or may not already contain duplicate values. If for instance I have ABC twice, I don't mind renaming them as ABC-1, ABC-2, but what if I ...
1
vote
1answer
1k views

MySQL UPDATE query stuck on “Sending Data” State

I am having difficulty getting an UPDATE query to successfully execute, due to the process getting stuck on "Sending Data" state. I feel this differs from similar questions about the "Sending Data" ...
1
vote
1answer
60 views

SQL server UPDATE statement is 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 ...
2
votes
1answer
36 views

Better way to update table with value from another table

I'm trying to rewrite the following query to make it more efficient: update c set c.cartype = (select Id from carTypes where carType = 'Coupe') from cars c inner join doorcount dc on c.Id = dc....
2
votes
1answer
71 views

Best DB approach for a lot of updates

Recently I built an app that gets that from SVN repositories to run a few metrics. What I do is basically: 1) Read data from an existing SVN repository and save this data to a database (with only ...
1
vote
3answers
240 views

Make another column in a table equal to the primary key column

In my notes table (MySQL database) I have id as the primary key. I want the column originalid to be the value of the primary key. Here is the query I run after creating a record which I thought would ...
1
vote
1answer
2k views

How to add/update a column with an incremented value and reset said value based on another column in SQL

I am stumped, I have searched and researched until my browser tabs no longer show the close button ("X") until you hover over them. I tried rewriting the search term over and over but I guess I do not ...
4
votes
2answers
415 views

Adding hstore entries to an uninitialized (NULL) column

I got bitten by this "feature" recently. If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error. Is this expected? create table ...
13
votes
4answers
3k 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 ...
0
votes
1answer
43 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
1answer
271 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 = ...
2
votes
1answer
1k views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...