Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [update]

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.

5
votes
1answer
69 views

Update Intersect hangs

We have this really weird behavior we just started experiencing with our update intersect statements. These were working fine, but now we are ingesting a pretty wide data source in terms of columns ...
1
vote
1answer
60 views

UPDATE table using CTE

I wanted to update the ranks of my table Performance and I found this solution, but I do not understand why this SQL code works: WITH cte AS ( SELECT AllTimeRank, r = RANK () OVER (PARTITION BY ...
2
votes
0answers
30 views

Need to update a table with an iterative number based on a subquery

Massive edit to follow procedure and enable reproducing this in a fiddler somewhere. Table 1, schedule: CREATE TABLE `schedule` ( `scheduleid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `...
0
votes
1answer
23 views

Postgresql update a partitioned table, where update affects multiple partitions

If I have a partitioned table partitioned on PKey column, can I update (or merge etc) the table like below (or more complex jonining to another table) which will cause multiple partitions to be ...
0
votes
1answer
33 views

PostgreSQL getting the latest value

I have started working on a postgreSQL database. There is a table keeping the record of stocks and every time a record is updated, a copy of the original row needs to be kept, with a flag added to say ...
3
votes
1answer
71 views

An uncommitted transaction updates a column; how can a different transaction see the old column value?

Let's assume the following case. There is a students table: id | name ----------------- 1 | Mateus A "User A" executes the following statement without committing the transaction: update students ...
7
votes
2answers
88 views

XQuery doesn't update XML data

I am trying to run the script to update an XML column: UPDATE DataImpTable SET serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1] with "9876"') ...
0
votes
0answers
21 views

UPDATE query in small tables taking forever

I'm having trouble creating a UPDATE query to perform the following: I have two tables, ed is a 11k rows table with areas from multiple constructions and a key lote_key wich links to the table unid, ...
4
votes
1answer
264 views

Seeing 2 very different execution plans for an UPDATE between test & prod environments

I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the ...
0
votes
1answer
35 views

How to update an element in json array with where clause?

I came across this question on how to update nth element of array, but it has nested json values in it instead of a plain array of strings. Here are some sample json values from jsonb column of the ...
1
vote
1answer
41 views

how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger?

SOLVED Edit: Trigger function and trigger are working fine with no errors, problem was with my database itself, dropped the database, created new, it's working now. How do I prevent UPDATE on any ...
1
vote
0answers
58 views

Deadlock on two update statements on the same page

I'm receiving multiple deadlocks in one SQL Server. The deadlocks occur when the same query with different parameter values is being issued on the same database: Example: Query 1: UPDATE Tabel1 ...
1
vote
2answers
29 views

update older records in each group

There is a set of data, like | id | serial | version | is_deleted | -------------------------------------------------- | 10 | AAAAAA | 2019-04-14 15:28:08 | 0 | | 22 | AAAAAA | ...
1
vote
3answers
71 views

Mysql innodb table dead lock while batch update in multiple thread

I've two tables of innoDB type and I'm updating one table using batch statement from 10 thread (one batch statement per thread) but some time I'm getting dead lock. java.sql.BatchUpdateException: ...
2
votes
0answers
103 views

Stored procedure to UPDATE row to “closest” match in one table from data in another table

I have a table called ThawLaneA that has a populated column ThawTime that has DATETIME value. The first row starts at a specific time, and each row from there "increases" by a specific amount of time (...
1
vote
1answer
289 views

MySQL - Creating sort index and update query takes forever

I have the following MySQL query UPDATE PCL AS p JOIN HST AS h ON h.SrNo = ( SELECT hi.SrNo FROM HST AS hi WHERE hi.SYM = p.INST AND hi.DATE >= p.Date AND hi.HP >=...
1
vote
1answer
78 views

Updating values in table based on ranges defined in another table

I have two tables definitions and history with the following schema: create table definitions ( id UNIQUEIDENTIFIER, revision INT, majorVersion INT ); create table history ( id ...
0
votes
2answers
44 views

Is there a way to speed up this update query using joins or any other method

I have an update query that selects data from a table (HST) and updates it into another table (PCLN). The PCLN table contains ~17000 rows while the HST table contains about 1.1 million rows. I have ...
0
votes
0answers
249 views

Unknown column “X” in field list on delete, but the column exists

I'm using MySQL and i'm trying to run a delete statement as follows: DELETE FROM `myTable` WHERE `nincidusua` = 84 and the following error occurs: Unknown column 'nincidusua' in 'field list' My ...
1
vote
1answer
15 views

Remove text from columns that may or may not be present multiple times

I have the following table in SQL Server 2008. create table OrgUnits ( OrgUnitId int not null constraint PK_OrgUnits1 primary key, OrgUnitName varchar(100), OrgUnitLevel int not null, ...
0
votes
1answer
210 views

change mysql datatype column from string to json

I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON. I ...
2
votes
1answer
95 views

How can I identify non deterministic updates occurring in my SQL Server?

SQL Server books online states Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement ...
0
votes
1answer
29 views

Return hours, minutes and seconds left if column wasn't updated

I currently have this query: UPDATE MyTable SET next_update = now() at time zone 'utc' WHERE next_update::timestamp < now() at time zone 'utc' - interval '1 day'; Which updates the column ...
0
votes
2answers
128 views

Optimize MySQL update statement

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.1 million rows. I have ...
0
votes
1answer
23 views

which one is faster: “insert on duplicate update” OR “update with case”

Table: CREATE TABLE t5 ( col1 VARCHAR(36) NOT NULL PRIMARY KEY, col2 int(11) DEFAULT NULL, col3 int(11) DEFAULT NULL, UNIQUE (col2) ); Records already inside: insert into t5(col1, col2, col3)...
0
votes
0answers
37 views

Match and update records in a table

I have two tables table1: | id | A | B | C | date1 | date2 | |-----|------|-----|------|--------------|--------------| | 1 | 100 | 100 | 100 | 10-Jan-2019 | 11-Jan-2019 ...
0
votes
1answer
130 views

Pass parameter to trigger dynamically

I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport. I have another table that lists some calculated statistics based on those values. I ...
1
vote
0answers
37 views

Postgres 10.6: UPDATE on partially indexed column sometimes hangs

I have a table (6 small columns 2M+ rows) with a column dead. There are two indexes on this table, both are partial with dead = false. From time to time I want to scan the table and mark rows with ...
5
votes
3answers
142 views

Is there any way to determine how many rows a transaction has modified (before the transaction ends)?

I have a ~320 million row table on which I run the following query: UPDATE my_table SET state = TRIM(state) Needless to say this has been running for 90+ hours (since thursday afternoon) and has not ...
1
vote
1answer
68 views

Correcting and Detecting overlapped dates with CTE

I have a CTE that marks (as bits) overlapped records based on certain criteria. After filtering, I need to update these records to "correct" them and make them sequential, but I'm getting a 4104 on ...
0
votes
1answer
48 views

Why is a trigger preventing update?

I have a script that runs weekly. It updates multiple tables in my database. It turns out one statement attempts to update 150,000 rows on a table that contains 250,000 rows. That failed this week ...
-2
votes
2answers
66 views

Are lost updates completely handled in SQL Server or is manual intervention still required? [closed]

As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first. We ...
8
votes
0answers
86 views

Why does my update statement update every row? [duplicate]

Problem: My final update statement should error out due to syntax and rollback the transaction, the expected error should be similar to: Msg 207, Level 16, State 1, Line 45 Invalid column name '...
0
votes
1answer
23 views

Aggregated JSONB result in update query

Introduction I created a toy example to extract the problem into a smaller part. Here is the schema definition with two tables users and events, users can have multiple events: create table users ( ...
1
vote
1answer
82 views

SQL simple update returning an error

Oracle SQL and Microsoft SQL Server:- I have a single table in which I wish to set two 'USERFIELD' column values, for all rows having their other column values set to a given literal. SET ESCAPE ON ...
1
vote
1answer
24 views

How to set Parent field according to another hierarchy field

I need some help to set a parent field according to hierarchy field like this: declare @Chart table([ChartId] int NOT NULL identity(1,1), [ParentId] int, [Number] varchar(5), [Serial] varchar(5),...
1
vote
1answer
33 views

Update rows in table with id from row before in PostgreSQL

Say I have a table like this: | id | grp | time_added | previous_id | |----|-------|------------|-------------| | 1 | 1 | 5 | null | | 2 | 1 | 8 | null | | ...
0
votes
1answer
26 views

need help in the update query

table name:- **t_ci** s39 column_name 176 i want to have my table like this s39 column_name 176 176 But this query works UPDATE t_ci SET s39=s345 WHERE blueprint_id=1234; But I ...
0
votes
1answer
116 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
1answer
1k views

Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification

I currently have a table which looks like this: CREATE TABLE "PDPC".collection ( col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass), q1 character varying(10000) ...
0
votes
0answers
38 views

What is the best way to update a few table objects of of database1 from the same structured table objects of database2?

Sorry, if I am wrong! I studied that import/export, dump, golden gate, db link can be used in oracle database logical objects updation. Please guide me in selecting proper method/tool for logical ...
1
vote
0answers
22 views

PostgreSQL update and select in one query [duplicate]

I have a table (id, serialnumber, status) of numbers and their statuses, need to take one with status 'unused' and change it's state to something like 'in work'. Can do it this way: UPDATE serials ...
0
votes
1answer
45 views

How to update one column in table from select statement?

I have a table that looks like this: utc date est date 2018-03-01 07:06:38.000 2018-03-01 07:07:38.000 est date is blank. I have a select statement that calculates EST time. ...
1
vote
2answers
370 views

How to optimize a table for a very high-frequency updates?

I have a table that contains a list of tasks that need to be run periodically: applaudience=> \d+ maintenance_task Table "...
0
votes
1answer
44 views

Checking update value before update

First time posting so apologies if this has been asked already. I have a table in SQL Server that is being updated from many different locations with data that I don't want in the table. I have come ...
0
votes
1answer
70 views

How to update a sequence column using single update statement

I have a table, lets call it foo which consist a column priority and has a unique constraint on it. lets assume below is the current state of foo table :- mysql> select * from foo; +-----+--------...
-3
votes
2answers
38 views

I need to copy values of a column from 1 DB to another DB on a diff column but has the same primary key

i need to copy the value of column of nickname and sysnbr to another DB on a diff table/column: I need to copy the nickname column and sysnbr column to this table. this is on another DB nickname = ...
1
vote
1answer
42 views

Update “oldest” row

I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n). I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
-1
votes
1answer
41 views

Improving performance of a query update that uses md5

I need to optimize this query, or change some configuration, because execution takes too much time. Query with rows as ( update hotmail.clean_us_usdataclean_1 set offers_excluded=case when ...
0
votes
1answer
18 views

Update record column with id found in the table of an associated model

Given three tables: A users table with id's: ╔════╗ ║ id ║ ╠════╣ ║ 1 ║ ╟────╢ ║ 2 ║ ╟────╢ ║ 3 ║ ╚════╝ A purposes table with user_id's ╔════╤══════════╗ ║ id │ user_id ║ ╠════╪══════════╣ ║ ...