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