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.
0
votes
1answer
20 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
34 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
54 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
54 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: ...
1
vote
1answer
114 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
70 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
41 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
30 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
46 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
78 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
28 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
94 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
20 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
59 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
30 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
136 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
55 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
45 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
62 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
79 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
22 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
25 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
71 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
633 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
237 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
36 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
37 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
40 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
32 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 ║
╠════╪══════════╣
║ ...
4
votes
1answer
81 views
An UPDATE runs unexpectedly on MySQL 5.7 whereas it runs as expected on prior versions
I use a column called sequence on tables where I have to re-sort blocks of rows into an arbitrarily order.
CREATE TABLE `tkmemberstage` (
`ID` bigint(22) unsigned NOT NULL AUTO_INCREMENT,
`...
1
vote
1answer
94 views
Should SQL Server 2000 throw an error if update statement fails within a SP?
I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was ...
-2
votes
2answers
621 views
How to UPDATE along with INSERT INTO SELECT [closed]
I added some entries from table2 to table1 as
INSERT INTO table1 (title) SELECT title from table2 WHERE ...
how can I UPDATE table2 for the SELECTed or INSERTed entries as
UPDATE table2 SET status='...
0
votes
1answer
30 views
How to accomplish such insert-or-update strategy?
Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better.
I've stumbled upon a business requirement that's kind of hard for me and I was ...
0
votes
1answer
47 views
Subtract multiple quantities in table2 from table1
How can I subtract the quantities in table2 from table1?
I'm using SQL Server 2014.
Table1
P_ID name quantity
1 carrot 100
2 eggplant 100
3 Avocado 100
...
0
votes
2answers
111 views
Understanding why a query is slow
I have a new column in SQL Server 2016, on a table of type bit. (I wanted it to be a persisted calculated column but was overruled and asked to make it a bit that is maintained by program code). Its ...
1
vote
2answers
185 views
Concurrent UPDATEs on INNODB tables in MySQL
I need to UPDATE all rows for multiple columns on a large INNODB table. Each UPDATE processes one column separately and takes about 1 hour. I have to do this for 10 columns so it would take 10 h I do ...
1
vote
1answer
443 views
Insert Trigger to a Linked Server error
I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the ...
1
vote
1answer
898 views
MongoDB update multiple sub documents inside array, each according to a condition
I have a document like this:
{
wid: 1,
items: [
{nid: 1, sent: 900},
{nid: 2, sent: 12},
{nid: 3, sent: 0},
{nid: 4, sent: 1}
]
}
Now consider I want to ...
0
votes
1answer
449 views
How to UPDATE from JOIN with GROUP BY
In a SELECT query of
SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0
How can I turn this query to UPDATE as
UPDATE b.number ...
