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 ║
╠════╪══════════╣
║ ...
