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.

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 ...