We’re rewarding the question askers & reputations are being recalculated! Read more.

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.

Filter by
Sorted by
Tagged with
13
votes
1answer
1k views

Cannot insert duplicate key row on a non-unique index?

We have encountered this strange error three times over the past few days, after being error free for 8 weeks, and I'm stumped. This is the error message: Executing the query "EXEC dbo....
1
vote
1answer
174 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
32 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
27 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 ...
-1
votes
1answer
31 views

SELECT Every Parent Table Record and INSET Multiple Record in Child Table Against the Parent_Id

Suppose I have two tables name company and screensavers as given below: **Company:** id (pk auto inc) name (varchar) **ScereenSavers:** id (pk auto inc) name (varchar) path (varchar) company_id (int)...
0
votes
1answer
36 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
72 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
102 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
22 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
266 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
52 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 ...
2
votes
0answers
1k views

Postgres bulk upsert performance

I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
1
vote
1answer
52 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 ...
2
votes
2answers
238 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
30 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
140 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
110 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
651 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
113 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
51 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
368 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
19 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
529 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
176 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
30 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
199 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
25 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
44 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
158 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
57 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
191 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
180 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
54 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
81 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 ...
0
votes
0answers
162 views

MariaDB Rows matched: 15135 Changed: 0 Warnings: 0

So... I find this to be strange: UPDATE cFlights c LEFT JOIN airports a on a.IATA = c.depCode SET c.depId = a.id, c.depName = a.name where c.depCode!='' and c.depName is null; I know that ...
8
votes
0answers
108 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
31 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
84 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
30 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
36 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
184 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 ...
1
vote
1answer
4k 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
26 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 ...
2
votes
1answer
47 views

find 16,000 words and replace with ##

I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i ...
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
584 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
49 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
273 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; +-----+--------...