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
2answers
61 views

Multiple Update with Multiple Conditions

We are told to watch out for things being set to null (we dont want) by using the IN clause in the following: UPDATE Tests SET TestScore = CASE WHEN TestId = 10 ...
2
votes
2answers
73 views

Very tricky: Can't update a sequential list SQL-92 ( Filemaker)

I have a quite simple problem, but the solution is very tricky. I'm trying to solve this for months: There is a column "SortID" that needs to get updated with sequential integers, starting from 1. ...
0
votes
0answers
53 views

Very Very Veeery slow insert queries

I am writing here because I`ve already spent days reading tutorials, other topics in here, and trying different configurations. The project : Fashion website with ~200k products which should be ...
0
votes
1answer
44 views

INSERT and UPDATE in Trigger

I have created a trigger so I could insert every row in a backup table CREATE OR REPLACE TRIGGER xyz AFTER INSERT ON table_1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO table_2 (...
1
vote
1answer
44 views

Mysql Update columns from same table columns

I have a table where payments are located with invoice_id and payment_id and also payment_due_date => payment receiving date and period_months that is total period of the payment I have added 2 ...
1
vote
2answers
49 views

UPDATE from subquery updates rows that shouldn't be updated

Using PostgreSQL, I want to update a column. The information how to update is in another table. My try was this, but it also updates rows I don't want to update: update table_1 set column_x = ( ...
26
votes
1answer
12k 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 ...
1
vote
1answer
52 views

Simple update takes forever

On SQL Server I want to update a 0/1 interger value for a specific set of records. For some reason this takes very long (let it run for 2 hours, still not finished). I can't figure out what is wrong ...
0
votes
1answer
20 views

sql update query for two connected tables [closed]

UPDATE rooms set r.vacant=true FROM reservation s,rooms r WHERE r.room_no = s.room_no AND s.cus_id=1 I created above sql queryto update room status.I want to set vacant as true when customer_id =1....
10
votes
2answers
11k views

How can I update a json element in postgresql json datatype

I can't figure how can I update an element in a postgresql 9.3 datatype. My exemple the db CREATE TABLE "user" ( id uuid NOT NULL, password character varying(255), profiles json, gender ...
6
votes
1answer
279 views

Locking in Postgres for UPDATE / INSERT combination

I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once. The user needs to be able to redeem a coupon, which will insert a row into the log table ...
0
votes
1answer
53 views

Updating data from subpartition when the subpartition name is unknown

Thanks to JSapkota who helped me pull the subpartition name as per below query select subobject_name from user_objects where data_object_id IN (select dbms_rowid.rowid_object(rowid) from TABLE ...
1
vote
1answer
38 views

Need help on UPDATE query [closed]

This is my table tracks id | art | time id is an auto-increment primary key art is a varchar(512) time is a timestamp not null predefined as current_timestamp and on update current_timestamp ...
0
votes
0answers
33 views

PostgreSQL not running every update on my script

I'm running a dozen update statements on a loop and some updates just won't happen (they return 0 affected rows), If i recall the script it will slowly update the rows, I don't know how to debug this....
1
vote
1answer
143 views

SQL Server Trigger does not update all table records

This is my first question so please go easy on me. We are using Access 2013 as a front-end and SQL Server 2012 with MS SQL Server Management Studio 11.0.2100.60 backend with ODBC connections. I am ...
1
vote
2answers
46 views

Find times when table is least frequently used

I would like to analyse performance of one of the big tables in the database. There are only two types of operations on it. Inserts and reads. Once data is entered it is never updated. I need to ...
0
votes
0answers
75 views

SQL Timeout when updating specific rows that goes away when other columns are updated

The data I'm going to provide won't be very complete, so I apologize in advance for that, and will happily supplement with more on request. I mostly just don't know where to begin looking, and don't ...
2
votes
1answer
109 views

Updating a table which contains an Image datatype column

I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF. I have a requirement to update one ...
3
votes
1answer
87 views

In a WordPress/WooCommerce table the prices are hosed. How to realign them? (a key=value structure)

We had in our WordPress/Woocommerce table (hence mySQL) ab_postmeta the sale (discount) prices for some products. We thought that reseting the sale prices via SQL query (and not via WordPress ...
2
votes
3answers
52 views

MongoDB multi update consistency

I have a collection similar to the below, of say 5,000,000 records. { "_id" : ObjectId("56d18f95d2961d1ddcb7244f"), "rec_id" : 0, "status" : "open", "updated by" : "A" } User B is trying ...
1
vote
0answers
41 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender ...
2
votes
2answers
74 views

Update function to enter random/unique GUID per row

I am new to SQL and am trying to do the following. i am using SQLite browser to edit the current file I have I do have a SQLite database with around 1500 rows of data that I would like to create a ...
0
votes
0answers
40 views

Updating 2M rows based on 2M individual rules (MYSQL)

I'm carrying out a fairly simple (but large) Database combine/migration (simple/dumb, but slow work). It's 2 systems that are very similar, but do have slight differences. Currently the main issue is ...
0
votes
1answer
48 views

mysql update based on select result

Here are two SQL statements. One is an update statement and the other is a select statement. I want to combine them. Update update customer_entity set website_id = 4 where website_id = 3 Select ...
0
votes
1answer
46 views

Select and update specific mysql data in single query

I am trying to first select data from a row which contains certain characters. I would then like to update and append new data into another row of those items that were selected. This is my current ...
0
votes
4answers
4k views

ISNULL() OR Is NULL in UPDATE statement

I have to run an UPDATE statement on live online database with about 5 lac records. I want to know which statement will run faster: Update Table set REC_ID = isnull(REC_ID,'') Update Table set ...
1
vote
0answers
32 views

MySQL Update column with case on joined tables

I have 2 tables and I want to update table 1 using a case check on table 2: update cms.Clients c #c_id unique join cms.ClientDomains d #c_id not unique on c.c_id = d.c_id set c.country = case ...
0
votes
1answer
15 views

Access IFf Query

I'm new and just starting out at creating databases so I apologise if noob question. What I am trying to do is update my Staff table to show that "Sarah Avon" has married and her new last name is ...
0
votes
0answers
56 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
964 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
86 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
296 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
878 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
71 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
43 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
207 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
43 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
30 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 I'...
0
votes
1answer
136 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
695 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"]}} , "publishTime"...
3
votes
1answer
52 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 ...
7
votes
2answers
1k 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
207 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
871 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
133 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
87 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
310 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 sys.dm_db_index_physical_stats(...
7
votes
5answers
1k 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 ...