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

6
votes
1answer
2k 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 care ...
0
votes
0answers
18 views

PostgreSQL updates hang when two servers involved

I have an update statement that works fine when only a single server is involved, but never completes when it comes from two servers. I'm connecting with JDBC 9.1-902 to a 9.3 database on CentOS 6.6. ...
0
votes
1answer
171 views

$addToset do not work in update if record not exists

I am trying to use following query db.TestColl.update({ "tags" : "silk" , "host" : "host.com"},{ "$set" : { "cat" : "MOVIE","publishTime" : 1370427540000 }, "$addToSet" : { "articleId" : { "$each" : ...
0
votes
1answer
11 views

How to update array fields in a table in Postgresql?

Say I have a table defined as following: CREATE TABLE my_tbl ( id bigint, a1 bigint[], a2 bigint[] ); I would like to create a stored procedure to append values to a1 and a2. Is this ...
2
votes
1answer
2k 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 ) = ( ...
1
vote
1answer
124 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 ...
0
votes
1answer
44 views

How to get number of locks acquired by my MySQL Query?

How to get number of locks acquired by my MySQL update query? Is it possible using MySQL Command Prompt or MySQL Workbench? Example query : update employees set store_id = 0 where store_id = 1;
1
vote
1answer
28 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 ...
0
votes
1answer
192 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 ...
1
vote
1answer
39 views

Is there something I can do to make this UPDATE run faster?

I have a relation EnumeratedSensorReading that includes a siteid and a quantityid column. I want to replace these two columns with a single sensorid column. I have added this column, and it is ...
1
vote
1answer
2k 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 = ...
1
vote
1answer
368 views

Updating large table (~6.5 million rows) & indexes

I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all ...
0
votes
1answer
351 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 ...
0
votes
0answers
10 views

Update or Insert on a Table based on a column value [migrated]

I have two tables BASE and DAILY as shown below: BASE Cust ID IP address 1 10.5.5.5 2 10.5.5.50 3 10.5.5.6 DAILY Cust ID IP address 1 10.5.5.5 2 10.5.5.70 ...
1
vote
2answers
118 views

SELECT and UPDATE in a single query while updating selective fields of duplicate records only

I have a MySQL table named Contacts_table with, say, the following contents: ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 ...
0
votes
2answers
50 views

MySQL - best practices/ how to speed up "update, else insert' queries? Insert + delete faster?

I'm running an ETL process and streaming data into a MySQL table. Now it is being written over a web connection (fairly fast one) -- so that can be a bottleneck. Anyway, it's a basic insert/ update ...
0
votes
0answers
24 views

Update MySQL column from column in another table

I have a table, products2013, with column upload_id which I would to contain the value upload_new_name from correlating table uploads (also containing upload_id). I tried putting together the follow ...
0
votes
0answers
13 views

MySQL - Update vs. Replace Into — why is Update non-parallel, and Replace Into allows multi-threads?

In trying to optimize a query that does (Insert ... on duplicate key update) .... someone suggested I do "Replace Into" to optimize performance. I'm not 100% certain the difference other than ... ...
0
votes
1answer
17 views

Update Column in table on midnight daily

I am designing a simple 1 table database in mySQL that will have two columns. One column will be the name of a specific restaurant on the webpage I will be using this database with. The second column ...
0
votes
1answer
170 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
0answers
13 views

Column Datatype Conversion Question [migrated]

I have a varchar column with text formatted like so "Sun Aug 07 17:43:55 +0000 2011" How would I convert that column in PSQL to a date datatype?
1
vote
1answer
41 views

Insert/ Update Scalability (more records, continually longer lookups — solution?)

posted this before but never received a clear answer. I'm working on an ETL project and am basically feeding JSON records of "tickets" into a MySQL database. (actually come to think of it, may be ...
0
votes
0answers
30 views

I have a table from which I can retrieve information but I cannot update or delete information

I can update and delete items via PhpMyAdmin. I changed the owner to mysql. My program is a clone of insert, update, and delete that is functioning for other tables within the database. I receive a ...
0
votes
1answer
38 views

MySQL: Updating or closing existing rows in a table and/or inserting new rows?

I want to update a specific table but in the following manner. When a record is completely the same (so not only the primary key, but all fields) then I don't want any changes (or a new record ...
0
votes
1answer
63 views

SQL Server 2008 R2 service packs

Are the SQL Server service packs 'cumulative' or 'incremental'? The scenario is that we have a couple of instances running on SQL Server 2008 R2 RTM and we have been asked to patch it up to SP3 ...
0
votes
2answers
27 views

Problem With update column in table [closed]

there is my problem. I have to update column "date_to" , but only for those users who havent set their last "date_to" to value '3000-12-31' . Table looks like this : ID date_from ...
0
votes
0answers
20 views

How can I use PostgreSQL's partial sum tree index (if it even exists)? Do any databases allow for use of such an index?

GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ...
0
votes
1answer
68 views

Rolling update to all rows in innodb table (no rollback if aborted)

I have the following table, which stores the Social Security Death Master File database (~86M rows): CREATE TABLE `death_master_files` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ssn_an` smallint(5) ...
0
votes
1answer
10 views

Building test site, update dates (back date records)

I have 3 tables that all connect and I need to back date them in an MS SQL 2008 setting. I was thinking about how to do this and not sure if I am on the correct path. The big problem I have is I have ...
-5
votes
1answer
34 views

Increment the value of an entire column [closed]

I have a table and one of the columns, is of the data type int. I want to increase every tuple's value of that specific column by 100. I have not found a proper answer for this yet. Anybody?
1
vote
1answer
63 views

updating same column as in the select query

When I try to do this, I get an error saying I cannot use the same table in where select as the column that I am updating. UPDATE table_name SET quantity = 19 WHERE productID = 148357 ...
0
votes
1answer
355 views

phpmyadmin not working after mysql update

i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal. But now there is another ...
2
votes
3answers
6k 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 ...
1
vote
1answer
43 views

MySQL Update a table based on the last data from another table while grouping with conditions

I need a query to UPDATE a series of fields based on another table Table lp_plates_backup | uid | brand | model | | 1 | | | | a | Old | Error | ...
0
votes
0answers
10 views

reorganize and UPDATE [duplicate]

This is my table called tickets with ID primary key ID-TicketID-TYPE 101-3-0 a02-1-0 01a-2-0 I wanted to UPDATE it and be like this ID-TicketID-TYPE a02-1-0 01a-2-0 101-3-0 but when I used the ...
3
votes
0answers
80 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 = ...
1
vote
2answers
60 views

Sybase ASE find running process

I run UPDATE INDEX STATISTICS on some HUGE table. And it runs already about 1 hour. Is there some procedures/ways to find the percentage of executing this command? sp_who, sp_sysmon don't help. ...
0
votes
1answer
77 views

Mongodb update conditional set operator ($set only when it is insert )

Is there anything in Mongo update where $set when will work only when an insert is done. example is I have a collection which has these fields: Username FirstOccurrence Count What i want that i ...
0
votes
1answer
39 views

UPDATE statement not consistent when including NULL fields

So I want to append Field B to C, then copy field A to B and set field A to "Awaiting update". I have written the following code UPDATE Notes SET /* append previous month to notes */ Notes=Notes + ...
0
votes
0answers
13 views

Checkpointing in a deferred update database

Something that I just cannot get my head around. Checkpointing writes all dirty pages from ram memory to disk. For Deferred update systems, data changes recorded in the log file are applied to the ...
4
votes
1answer
2k 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 ...
0
votes
0answers
30 views

PostgreSQL slow update -> does autovacuum have anything to do with it?

I am quite new to PostgreSQL as I only need it to create a map (using PostGIS). So I don't really know on hot how to optimise my DB... I have a table with a lot of polygons that I'd like to save in a ...
0
votes
0answers
30 views

Oracle SQL Updating Trigger Statement

Hey all so let me see if I can clearly explain what I would like this trigger to exactly do. First of all the trigger will deal with 2 tables: Delivery Table: DeliveryNum |ItemName | Quantity | ...
0
votes
1answer
62 views

How to limit the range of an insert-update to only look at certain rows/ rows with field value: X?

I posted something similar before, but am still unsure of the technical implementation --- I'm so close to being there to the finished product. I have a table of tickets, and a stream of ticket ...
5
votes
2answers
731 views

Adding a sequence number to existing rows

I have a table with an OrderSequence column. The schema looks roughly like this: id Name OrderSequence I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the ...
1
vote
1answer
67 views

MySQL Deadlock - accessing different primary key values also creating deadlock

I went through MySQL logs and found the reason for deadlock 1st thread is trying to execute below query. Which is waiting for thread 2. UPDATE M_SAMP SET MM_Q_IND=0 WHERE M_ID IN (SELECT M_ID FROM ...
0
votes
0answers
22 views

Efficient Trigger updating in Postgresql

So I have a big table that often needs specific columns updated for a large number of rows. So right now I have it set up so that there is a 'staging' table that has the update-able columns. Then upon ...
0
votes
0answers
26 views

How to find Deleted and Updated record tables in MYSQL database?

I have scenario like below. I need to find Deleted records and updated records effected tables in mysql. Ex: I update my Name or Email Id in UI that is effected in some tables either one table or ...
0
votes
1answer
44 views

Is it possible to update a field value in one table based on data inserted into various other tables?

Suppose I have a table representing clients. There is a field called last_active in the table which contains a time stamp. When a client uses the database they can insert data into one of four ...
0
votes
1answer
95 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 ...