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
4 views

Do databases make a delete and an insert when having to update rows?

So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something ...
0
votes
2answers
157 views

using update query with subquery in Oracle sql

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement ...
2
votes
2answers
61 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: ...
7
votes
2answers
620 views

Update all rows

I want to know the most efficient manner to update every row in an extremely large Oracle table for a single column. For example: update mytable set mycolumn=null; or: update mytable set ...
2
votes
0answers
20 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
0
votes
1answer
14 views

Can't figure out how to update a column in one table based on multiple boolean values in another table in postgresql

What I want to happen is boolean milestone.milestonecompleted will turn true only if ALL of the linked boolean task.taskcompleted values are True I've looked around and come up with this. Any help ...
0
votes
1answer
300 views

Tracking the update or changes in mysql table

I need to stream the changes to elasticsearch to keep in sync with my database(mysql). What will be best way to do this ? I have thought for below approaches Using trigger( but my table will have ...
0
votes
1answer
48 views

Why can't I update or delete certain records in SQL Server?

I am having a weird issue in SQL Server 2014. I cannot update or delete certain rows from a table. I can insert rows fine, and select them, but when I try to update or delete a row, I get a timeout ...
3
votes
2answers
48 views

MySql - Ranking so it only needs to be done once

By reading around, this seems to be a reasonable way of ranking people. The table: UserId (int primary key) Name (varchar(100)) Score1 (int highest scores rank 1) Score2 (int, if Score1s are equal, ...
-1
votes
1answer
55 views

UPDATE statement in a trigger occasionally updates some columns but not others [closed]

I have a table called NOTATION with the below table structure: ( NOTATION (primary key) TABLE_NAME , TABLE_KEY , NOTE_TIMESTAMP, SUBJECT , COMPANY , ...
0
votes
1answer
108 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
35 views

Updating column from another table - PostgreSQL 9.2 [closed]

There are two tables: ja_jobs junk.test_test1 I need to update the time_job column from junk.test_test1 to ja_jobs. Here is what I'm doing now: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM ...
1
vote
1answer
19 views

Remove accidental duplicates in PK table, cascade changes to FK table

Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and ...
0
votes
2answers
32 views

MySQL - An UPDATE from a SELECT

The more I learn about about the great things you can do with mysql, the more I keep pushing to learn. I have two tables: Tests TestNumber (int primary key) InactiveTestSlotBitwise (int) ...
0
votes
2answers
4k views

Update a column with values from the same newly inserted row in a trigger

Using SQL Server 2014, I have this: Table1 Tab1_id int identity(1,1) primary key Tab1_val1 smallint Tab1_val2 smallint Tab1_valInfo varchar(10) -- not yet implemented, will be used ...
3
votes
2answers
55 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 ...
1
vote
1answer
173 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is ...
2
votes
2answers
60 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
1answer
1k 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 ...
0
votes
0answers
49 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
38 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
36 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 ...
0
votes
1answer
418 views

MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

I had a table with ~0.5M rows and tried to exec simple query, something like UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999 but result was ERROR 1297 (HY000): Got ...
1
vote
2answers
41 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 = ( ...
24
votes
1answer
10k 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
49 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
19 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 ...
10
votes
2answers
10k 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
238 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
28 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 ...
2
votes
1answer
177 views

Updating large table worked before, now taking hours

BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not ...
0
votes
1answer
26 views

Adding and populating column in Postgres with data from existing column

I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with ...
1
vote
1answer
35 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 ...
-2
votes
1answer
44 views

Cannot update nor insert data to datetime field [closed]

I was trying to insert data through one web application I am developing and I could not insert data, while I was troubleshooting what the problem is, I saw that I can not neither edit datetime field ...
0
votes
0answers
32 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 ...
1
vote
1answer
86 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
45 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
55 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
69 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
73 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
42 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
30 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
60 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
39 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 ...
3
votes
1answer
15k 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 * ...
0
votes
1answer
41 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
33 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
3k 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 ...
0
votes
0answers
30 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 ...