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
14 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
39 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) ...
-2
votes
1answer
33 views

How to check a value exist in column and update [closed]

I'm actually doing a leaderboard but encountered a problem I'm trying to check in my db if a player already exist and if his new score i greater than the previous one update it: $query = mysqli_query(&...
1
vote
1answer
62 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
46 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
30 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
35 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
30 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
16 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
37 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
9 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
46 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
23 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
14 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
36 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
20 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
30 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
386 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
59 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
49 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
62 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
115 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
34 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
26 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
26 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
vote
1answer
37 views

How do you combine multiple update statements for the same row using MySQL trigger

Background: Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger. Code: Here's a simplified ...
0
votes
1answer
96 views

Does update lock the row or the entire table?

Basic question: When I update a row in a database table, does it lock the current row or the entire table? I am using MySQL. When I update column which is not unique. Is the locking different from one ...
1
vote
0answers
35 views

Create a Postgresql trigger that updates column with PK of other table

Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback. I'd like to ...
2
votes
1answer
47 views

MySQL Best approach for recording last update timestamp for every column and every row

This question is about possible approaches for time stamping every column of every row in a table. I have a table with a small number of rows (fewer than 200 and not likely to grow much larger). Each ...
-1
votes
1answer
41 views

How to update records of two tables through trigger?

I have two tables as employee and employee_backup whenever i update employee table the trigger must update employee_backup table also : this is the piece of code i was trying to achieve specified goal:...
1
vote
1answer
26 views

Mysql How to update n rows using count function?

In the following table, I want to set delete = true if the total records for same orgid exceed 500 and I want to do it according to createdate such that if records exceed 500 the old records get ...
1
vote
1answer
24 views

Delete duplicate records and update the mapping table

I have store_table_map and item_price_map. I want to delete duplicate records in store_table_map and retain the latest row based on updated_date because i want to put constraints in store_id and ...
0
votes
2answers
43 views

Fastest way to update column value

I need to set all values of a given column foo to a constant varchar bar. Is there a faster way to do that than with the vanilla UPDATE mytable SET foo = 'bar'? Facts: This is an Oracle MySQL 8.0.20 ...
1
vote
1answer
15 views

Mass update all cells in a column with a substring of it's original value

I need to trim the last seven characters off of all the cells in a column. This query returns the results that I need SELECT test_date, SUBSTRING(test_date FROM 1 FOR CHAR_LENGTH(test_date) - 7) as ...
0
votes
1answer
23 views

updating n number of records mysql database

I want to update n number of records in table using a query like below, but it will return an error since in mysql, it is not allowed to use value after the limit key update producedItem pi inner ...
0
votes
1answer
15 views

Update main and second table with a before update trigger for message logging

I currently have the following testing schema: CREATE TABLE userMessage ( message_id BIGSERIAL PRIMARY KEY, creator_id BIGINT NOT NULL REFERENCES "User" (id), created_at BIGINT ...
1
vote
1answer
59 views

Update dynamic jsonb array of objects

I am trying to update a jsonb key with a new value, but I cannot update all the keys at once. My json structure is something like this: [{ "type": "button", "content": { "align": "...
0
votes
0answers
18 views

Which aspects of PostgreSQL `UPDATE` do impact IOPS?

Premises: there is a big table (hundreds of millions of rows), a new column is added to the table and needs to be populated with values. UPDATE by chunks (e.g. id ranges of different sizes) causes ...
0
votes
0answers
19 views

How does Postgresql UPDATE work internally?

How does Postgresql UPDATE work internally? In some questions on SO/DBA I found that technically it works like DELETE/INSERT though in the docs it is only said about partitioned tables: In the ...
0
votes
1answer
33 views

Why do these queries deadlock and how to work this around in MariaDB?

The following 2 queries enounter DB deadlock in my program: DELETE FROM proxies WHERE NOT EXISTS ( SELECT 1 FROM proxy_xref_proxy_list AS pxpl WHERE pxpl.ProxyID=proxies.ID AND (pxpl....
0
votes
1answer
46 views

Update a Column with sequential number based on the order by of a column in Oracle Sql

I have table with below create scripts create table training( action_id NUMBER not null, training_id NUMBER, course_name VARCHAR2(100), display_order NUMBER default 0 )...
1
vote
1answer
20 views

UPDATE table element using data and id from another table

I need to update an element the row for id=N in table A with derived data from Table B. Table A has uid as its primary key, and contains DATE element foo. Table B has a_uid as an element, for each ...
0
votes
0answers
11 views

mysql queries in 2 times (select) + 1 time (Insert) verifications handle problems for db to dont create double inserts

I want to make an 2 way select verification for all users in that kind of code version: select * from table_A where other_user_id='B' if (1) -> update set response_A='1' if (0) -> select * from ...
0
votes
0answers
108 views

Bulk INSERT … ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB

I have a table like the following: CREATE TABLE `demo_upsert` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci', `LastListID` BIGINT(20) NULL ...
1
vote
3answers
90 views

Postgres- update a value in a array on particular index

I have a column named ids that is of type bigint[]. ids cane be null or of any length It has values based on certain hierarchy. My use case is I want to update value that is at a particular index. ...

1
2 3 4 5
18