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