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
18 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)...
-4
votes
0answers
31 views

Why doesnt my query work [on hold]

The following query does not work can someone explain to me why? UPDATE rating SET `count`=`count`+1 WHERE osubjectID="1" AND classID IN (SELECT `classID` FROM student WHERE `studentID`="2")
0
votes
0answers
34 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
0answers
14 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
18 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
121 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
43 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
41 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
60 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
76 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
20 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
23 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
32 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
24 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
47 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
116 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
20 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
153 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
42 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
24 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
35 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
38 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
28 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
80 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
89 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
219 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
26 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
35 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
107 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
117 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
186 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
470 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
91 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 ...
0
votes
1answer
56 views

Is it harmful to update columns which aren't changed?

Say I have a table with some columns: CREATE TABLE t ( key int PRIMARY KEY, c1 int, c2 text, c3 timestamptz ); I now fetch a row from the table: SELECT * FROM t WHERE id = 180; ...
1
vote
1answer
39 views

I Want To insert data in transaction table and at the same time update customer table in codeigniter

(codeigniter) I Want To insert data in transaction table and at the same time update customer table . I want to Add value of ChangeDue in transation table To value now_debt in Customer Table ...
0
votes
2answers
197 views

UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT'

I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table. To me, the query is ...
2
votes
1answer
229 views

Temporarily disable indexes before updating whole table

For testing purposes I periodically restore production backup into a new database (AWS RDS PostgreSQL instance) with pg_restore. In the new database I run SQL queries that anonymize sensitive data. ...
0
votes
1answer
37 views

Update only if entry does not exist

I have to update many records. I don't want to duplicate the entries. The table concatenates the value to the end. So in theory, I could add the value twice. I want to put a check to make sure the ...
1
vote
1answer
46 views

Why is this very simple UPDATE so much slower than the SELECT it contains?

In MariaDB 10.3.9: SELECT COUNT(*) FROM photos vs UPDATE counts SET countValue = (SELECT COUNT(*) FROM photos) WHERE countName = 'photos'; The select takes 1.1s vs the update which takes 2.6s. ...
1
vote
2answers
196 views

Update single field in a document (document.update($set)?)

Can't seem to figure out how to update a single field. Whatever I try, to update one field (picture: "url") the rest of the fields are being removed and set to null. I use the following command now: ...
0
votes
1answer
22 views

Scrambling PII data in CLOB columns

I am working on scrambling PII tags in CLOB columns such as <FirstName> or <LastName> or <SSN_Number>. so I am using the below query to achieve the same. UPDATE /*+PARALLEL(8)*/ ...
0
votes
1answer
26 views

What is the best solution to prevent picking the same value (by multiple requests)?

Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick ...
0
votes
2answers
59 views

creating a trigger after update exclude a column?

I want to create a trigger that set a column to to_sync true if the other columns has been changed. CREATE TRIGGER [dbo].[product_trig] ON dbo.product AFTER UPDATE SET NOCOUNT ON; IF NOT ...
1
vote
1answer
42 views

What is the function that determines the maximum value in a series?

Say I have a table with columns C and D (among others but these are the relevant ones) with the date type. I am instructed to update the values in C to be 90 days before its corresponding value in D, ...
3
votes
2answers
34 views

How can I instantly update a table in a database with the columns values from another database?

I have database_1 and database_2 both on the same server. How can I instantly update the USER table in database_2 when data is inserted into the USER table in database_1? Structure of both tables: ...
0
votes
1answer
533 views

Trigger to check if a column is updated and another one has a specific value

I'm writing a trigger for a SQL Server database. What I want is, when a table is updated, check if a specific column is updated or not and if another column has a value = 'W' or not. If it is so, the ...