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.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
18