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.
876
questions
1
vote
1answer
5 views
Mysql update table from another table with condition
in mysql i have 2 table.
First wp_postmeta_import
Second wp_postmeta_temp
This tables have this column:
meta_id
post_id
_ meta_key
_ meta_value
I need to update only my column meta_value of ...
1
vote
2answers
18 views
How do I simultaneously update unique keys in PostgreSQL?
CREATE TABLE widget (
id serial PRIMARY KEY,
name text NOT NULL,
ordinal int NOT NULL UNIQUE
);
I have the data
id | name | ordinal
----+------+---------
1 | A | 1
2 | B | ...
1
vote
2answers
12 views
MySQL: Updating large data set including time aggregation
I am having a large MySQL (5.7) table with millions of rows (contains data for each second). Based on these values calculations should be performed, in some cases for large data ranges. Therefore, I ...
0
votes
0answers
12 views
Does MySQL have a built-in way to tell whether a view is insertable (not just updatable)? E.g., something like the IS_UPDATABLE flag, but for INSERT?
The MySQL documentation for the INFORMATION_SCHEMA.VIEWS table mentions that MySQL sets a flag whenever a view is created that records whether that view is updatable, i.e., whether you can UPDATE the ...
0
votes
1answer
79 views
SQL Trigger to Insert a row in Table B is the record is inserted in Table A [closed]
I have a requirement where I have to Insert a Record into Table "Site" after the record is inserted into InventOperationalSiteStaging Table. I created a SQL trigger which is working fine for ...
1
vote
1answer
45 views
PostgreSQL Trigger to Update Inserted Row With Data From Another Table
I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels & meta-data).
I am trying to create a PostgreSQL trigger to update the ...
1
vote
1answer
11 views
sqlite 3.4 updating table with data from different table - changed with 3.3 - newbie question
All of the other questions on this seem to be before 3.3 when sqlite added an update join feature? Newbie here, so I may even be getting the terminology wrong. I am trying to update the issbn field in ...
1
vote
1answer
24 views
SQLite: Using count in triggers to update another column
UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that ...
1
vote
1answer
43 views
postgres query performance [closed]
I can't find a good way to rewrite this query or improve the performance:
query:
update company_holding c
set issuer_stim_buy_quarter = sq.estim_quarter_buy
from (select h.company_id
,h....
1
vote
1answer
32 views
How can I make UPDATE to return an error if no rows are affected in Postgresql?
In my application code, I have an ordinary UPDATE statement to update some row's properties. However, I've encountered a bug where this statement was provided with an incorrect ID and no row was ...
1
vote
2answers
39 views
How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014?
For example, I have a table of:
id | code | name | type | deviceType
---+------+------+------+-----------
1 | 23 | xyz | 0 | web
2 | 23 | xyz | 0 | mobile
3 | 24 | xyzc | 0 | web
4 ...
6
votes
1answer
155 views
Updating an indexed view with NOEXPAND
I will preface this by saying that this is a cross post from an unanswered question on Stack Overflow.
I am not doing this simply to get more views on the question, I would like the DBA community to ...
4
votes
1answer
98 views
Syntax shorthand for updating only changed rows in UPSERT
I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed)...
0
votes
0answers
32 views
Is it possible than two simple updates on same table interlocks (deadlock)?
Table A is:
id integer
version varchar
data jsonb (large data 1mb)
fkToBid integer (references B.id constraint)
Table B is:
id integer
other...
Processes are aggressively running the two updates ...
1
vote
1answer
46 views
Postgres 9.5 deadlock with many updates on a table + many “select for update” on another table
I have two tables: "machines" and "magictools". "magictools" refers to "machines" with a foreign key.
I face a deadlock problem while executing many of these ...
0
votes
2answers
40 views
MySQL Insert Values Into Normalized Relational Database (multiple tables) at the same time
I have normalised my database to 4NF. How do I now insert values at the same time when records have been split into multiple tables? most of the child tables reference their parent table CUSTOMER ...
1
vote
1answer
47 views
Receiving error “invalid identifier” while trying to update a table in Oracle
I'm trying to transform a Merge query into an Update query for getting better performance but I could not succeed.
Tables structure:
create table TEST_FILE_MERGE
(
customer_num NUMBER,
...
0
votes
2answers
35 views
Make two different updates with two different where conditions
Although this question is very similar, I don't think that it is also the best approach for my case, though it seemed to work. Consider the following example:
CREATE TABLE messages (
writer VARCHAR(...
1
vote
2answers
75 views
How can I efficiently delete records when a counter reaches zero in PostgreSQL?
Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this ...
0
votes
1answer
45 views
Unexpected unique constraint violation during update
In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records ...
2
votes
3answers
254 views
Query to update one column of a table based on a column of a different table
I have two tables with below structure :
Create table Customer_info
(Customer_num varchar(50),
Customer_Branch int);
Create table Customer_Transaction_Info
(Customer_num varchar(50),
...
1
vote
1answer
41 views
Mysql JSON update by value of a key
I have a table like:
CREATE TABLE `campus_tb` (
`campus_id` int(11) NOT NULL AUTO_INCREMENT,
`campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`...
1
vote
1answer
54 views
MS SQL Server 2019 - deadlock after row update by primary key, possibly caused by trigger
I've a table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](65) NOT NULL,
[nickname] [nvarchar](65) ...
1
vote
1answer
79 views
Inserting Data using Cursor but check if the data already in the table
I want to check if data already exist in a table before I do an insert using a cursor. So the cursor will pick a record, check that record doesn't exist in the table. IF it Doesn't EXIST then Insert, ...
0
votes
2answers
49 views
Merge two separate UPDATEs into one
I have two separate UPDATEs as follows:
UPDATE mytab
SET colA = 1
WHERE id IN ( SELECT id FROM mytab
JOIN othertab ON mytab.otid = othertab.id
WHERE othertab.Lock = '...
0
votes
0answers
31 views
MySQL - how to optimize select within a range bound by values in 2 different columns - innodb
How do I optimize select within a range bound by values in 2 different columns with MySQL innodb? I'm matching words to the pages on which they were printed.
Each instance of a word points to only 1 ...
0
votes
2answers
59 views
Updating a column name to be a concatenation based on the same table
Need Help in SQL SERVER!
I have two tables: Students: Student_Id, Student_Name
Grades: Student_Id, Profession, Grade.
I need to change the column Profession to have the value Profession + Grade only ...
0
votes
1answer
40 views
Nested push in Mongoose
I have a user schema like that:
name: String,
nick: String,
email: String,
password: String,
bio: String,
color: String,
followers: [String],
followings: [String],
posts: [{
author: {
...
0
votes
0answers
29 views
Performance issue with update in postgres
I've got a pretty simple update statement in my application in one of the APIs.
UPDATE "table" SET "count" = "count" + 1 WHERE "id" = $1
The problem is when ...
0
votes
0answers
17 views
Can you use a SELECT … FOR UPDATE cursor in a stored procedure to enforce row-level locking?
From https://www.oreilly.com/library/view/mysql-stored-procedure/0596100892/re37.html:
CREATE PROCEDURE needy_bonus( )
BEGIN
DECLARE v_employee_id INT;
DECLARE v_salary ...
0
votes
1answer
42 views
PostgreSQL update column in one table with value from another, based on key from first table
Sorry about the title, I wasn't sure how best to word this. My situation is this:
I have two tables:
paper_author [paper_id, author_id, author_name]
authors [author_id, author_name]
Right now, the ...
0
votes
0answers
10 views
How to apply many UPDATE statements in one network call
Can we apply many UPDATE statements in a single network call?
Figure many UPDATE requests on a JSONB field of one record.
I would like to update many places in the JSONB field.
All updates apply on ...
0
votes
2answers
49 views
Why does my table keep getting corrupted after removing large amounts of data?
I have a database that is about 400 gigs. The majority of the space is consumed by images of utility bills stored in string form as varchar(max) in a single table. It's a poor database design that I ...
0
votes
1answer
25 views
What is the effect of number of CPU cores on update operations in Postgres?
I have batch update operations being sent to the PostgreSQL server to be executed. Each operation (batch) will be updating rows in the same table but it is assured that they will be updating different ...
0
votes
1answer
24 views
PostgreSQL update table then insert on another table
I have a User table and a Log table. I need to update a field on the User table and insert a new entry on the Log table recording the update. I have written the statement for the update:
UPDATE ...
0
votes
1answer
37 views
What is a good strategy for updating lot of rows in postgersql?
I have an inventory table which contains item_id and the quantity remaining of the item (And also some other meta data). I have an administrator update the inventory by uploading a CSV file which ...
1
vote
1answer
37 views
update most recent rows only?
I have a database of a garage of rented cars with a table called Cars with the following columns :
Type,First_seen_in_garage,Last_seen_in_garage
The table initially have the following rows :
Ferrari,...
0
votes
1answer
38 views
Should an update statement query plan ever go parallel?
If an update statement's query plan does go parallel, does it guarantee a communication buffer resource deadlock, or is that determined by whether the parallel plan is using all cores?
0
votes
1answer
21 views
Simple Update Join much slower than it should be (MYSQL)
This is a simple Update Join that updates only about 100 rows:
Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020'
This takes about 30 seconds to run, despite the fact that:
...
0
votes
2answers
146 views
Update postgres json value by other attribute
I'm using PostgreSQL 9.6 and
I got the following table:
Cars
ID Name Data
1 abc {"color":"red","wheels":4,"my-value":false}
...
8
votes
2answers
433 views
UPDATE statement behavior
I have a question regarding inner workings of UPDATE statements with regards to SQL Server. I am trying to understand what will happen if the following 2 update statements are received or serviced at ...
0
votes
2answers
60 views
How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?
I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys ...
0
votes
0answers
53 views
Making a trigger to update FK based on columns in another table and an external table
I'm inserting data into a database and I want the FKs to be populated automatically based on other information that links each FK to its corresponding PK.
Here's my plan:
Create an external table of ...
0
votes
1answer
155 views
INSERT .. ON CONFLICT DO UPDATE SET error
When I use the following query :
cur.execute("""INSERT INTO TABLE
(SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp
FROM tmp_table)
ON CONFLICT (ID)
DO
UPDATE SET Last_seen=...
0
votes
1answer
118 views
Query tuning update with hash flow distinct operator
Need some help in understanding the slowness in one of the UPDATE statements like below:-
UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc....
0
votes
1answer
37 views
SQL Server :: how to move country code column to another table?
I have 2 tables:
tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG).
Like this:
CountryName TwoCharCountryCode ...
0
votes
1answer
37 views
Why is ALTER faster than UPDATE (for full table rewrites)?
Let's create a garbage table..
CREATE TABLE foo
AS SELECT x::text FROM generate_series(1,20e6) AS t(x);
Time: 14077.654 ms (00:14.078)
Now, let's look at boring UPDATE,
UPDATE foo SET x = 'foo';
...
-1
votes
1answer
32 views
Single query to replace a value in multiple tables with same column
I am using Postgres SQL. I am having multiple tables say table1, table2, table3, table4, ... with a common column say name in each of the tables. I want to replace a the value 'Jhon' with 'Jimmy' in ...
0
votes
0answers
28 views
insert or update with summary subquery
i have a problem with follwing query
INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,...
1
vote
1answer
33 views
SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row
Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server's databases. I ...
