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.
917
questions
5
votes
1answer
320 views
Are TOAST rows written for UPDATEs not changing the TOASTable column?
Imagine I have a table in Postgres 13 like this:
CREATE TABLE public.people (
id integer PRIMARY KEY,
full_name character varying(255),
bio text
);
I then insert a row with enough ...
0
votes
2answers
32 views
Update with subquery referencing the main table
I'm trying to understand how to filter my subquery in context to the main query. Ultimately I'm trying to get the MAX value from the latest record BEFORE the date of the current record. Here is where ...
0
votes
0answers
25 views
Unexpected timestamp value in updated_at field of table [closed]
We're using an ORM for maintaining timestamp fields in a table. There are around 100 million entries done in the table per day. We're seeing an unusual behaviour where updated_at field in some of the ...
0
votes
0answers
37 views
Updating one table with data from another
I'm trying to update date a WordPress column with data from a table I've imported DB version is "mysql Ver 15.1 Distrib 10.3.31-MariaDB". Should be simple, yes?
The imported table as two ...
0
votes
0answers
19 views
How to run both "before" and "after" update in trigger
I need to calculate prior values in a table before the column values are updated. Basically, I have to calculate the difference between the before the update and after update values. If there is any ...
0
votes
0answers
29 views
tsql do update queries that join other tables lock the joined tables also?
Azure SQL Managed Instance. If I am updating a temp table, and joining to another table or tables, do the joined tables also get locked, even though the update statement is only updating the temp ...
0
votes
0answers
46 views
Oracle multithreaded JDBC bulk select high-performance data export
The goal of our solution is to export as fast as possible CLOB data from an events table. The concept is to logically partition the data with a partition_id column such that every thread from the ...
0
votes
2answers
34 views
Store value of subquery in update for later use
Trying to calculate % change of price. Query below works, but was hoping there was a way that I didn't have to call the same subquery first? I don't think WITH works here unless I'm mistaken.
update ...
0
votes
0answers
22 views
What is the intended method for automatically/streamlinedly updating minor/patch PostgreSQL versions on Windows?
I currently run PostgreSQL 12.4 on Windows 10. Since the "BigSQL" distribution is no longer around, the only choice is to use the EnterpriseDB installer for the initial installation: https://...
0
votes
1answer
33 views
Best practise to compute total amount from individual entries
I want to keep track of the individual and total amount of drinks someone ordered.
Table 1 Customer information:
customer_id name age
1 Steve 44
4 John 24
Table 2 Drink ...
0
votes
1answer
21 views
Postgres preventing update on "events" table but insert "warning" event
I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database.
My dilemma is as follows: I have an event table that logs basically everything that happens on my ...
2
votes
1answer
73 views
PostgreSQL relative update breaks unique constraint
I have unique index on column for UPSERT. When I am trying to update column using v = v + 1 expression my unique index breaks.
SQL
CREATE TABLE test(v bigint, data jsonb DEFAULT '{}'::jsonb);
INSERT ...
0
votes
1answer
12 views
Slow group by execution after update to MySQL 8.0.26
I am running a larger DB with tables > 200M datasets on a dedicated Ubuntu 20.04 machine.
Since a few days queries that use group by are extremly slow. I suspect that this is new since the last apt-...
0
votes
0answers
32 views
UPDATE rows and SET ordering depending on level and parent
I'm trying to write a small UPDATE query that makes sure all rows are reordered considering the parent_id.
Needs to work under MySQL 5.6 / MariaDB 10
This is a DB Fiddle made for testing.
So this is ...
1
vote
1answer
34 views
Can I split text from one column into to two existing columns in My SQL [closed]
SELECT `Appointment`,
SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 1), ' ', -1) AS Date,
SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 2), ' ', -1) AS Time
FROM go_applicants
...
0
votes
0answers
14 views
Update a field of type jsonb[] - postgres
Dear Awesome community,
I have a table as follow:
id | keys | typeid | sectorid | commitid |
--------+-----------------------------------------------...
0
votes
4answers
175 views
Slow CTE update query
I have a query that is running for more than three hours without completing.
I realized that the bad part is the Filter operator which has a cost of 89%.
WITH
FASES_NUMERADAS_CTE AS
(
...
1
vote
1answer
51 views
Optimize UPDATE query for single "next" row
We had information from new relic that one our query has a bad performance (almost 16 seconds for execution). We use PostgreSQL 11.8 on Debian Linux.
Table:
postgres=# SELECT table_name, column_name, ...
0
votes
1answer
31 views
Updating a column value using a sub select join to the same table
The table BillOfQuantitiy has level index and boqitemname, sitename and many more. The data visualization is as follows
|Level Index | Boq Item Name | Site Name|
|---------------|---------------|--...
-2
votes
2answers
63 views
Oracle Update Left Join
I have this MYSQL code butt I am unsure how to convert it to oracle database. Any help would be greatly appreciated!
UPDATE product p
LEFT JOIN (
SELECT product_name, COUNT(*) AS quantity
FROM ...
6
votes
5answers
1k views
MySQL: Swap ID between two rows
We have existing table:
| id | name | color | calories |
------------------------------------------
| 1 | apple | red | 20 |
| 2 | orange | orange | 10 |
| 3 ...
0
votes
1answer
28 views
Postgres per-statement update trigger relationship between old and new transition tables
Is there anyway to associate rows between the old transition table and new transition table for a per-statement update trigger in Postgres? docs
My use-case is that I have a "shadow" table ...
0
votes
0answers
39 views
Generic update of "normal" columns with data from JSON
I am trying to do a "generic" insert/update of table columns using JSON. There are tons of info how to update a JSON column, but I have found none that updates several normal columns in a ...
5
votes
2answers
155 views
Unexpected update results on heap using SET @Variable = Field= @Variable + 1, fixed with clustered index
I'm just looking to understand why this is happening, and my Google searches were failing me. We are on SQL Server 2016 SP1.
This is the situation: Vendor table that manages IDs by keeping track of ...
0
votes
1answer
20 views
Mongo - How to update a property of a document using a nested value of the same document
I want to update a document using a nested value from that same document. So if I have an document that looks like this:
{
_id: ObjectId("60b002d490b01b63eee5d4b7"),
title: 'foo',
...
1
vote
1answer
33 views
UPDATE SET N + 1 equal to a row (with iteration)
Question: How would you iterate over the previous result (referenced below) over for each of the values in column b3?
MariaDB Fiddle (this includes prior fiddle information to solve the previous ...
3
votes
2answers
61 views
UPDATE SET N + 1 equal to a row
Problem: I need help implementing N+1
WHEN a2 = "Z" then check if the next row number (a1 + 1) has a2 = "Z" ...if this is the case THEN replace the (a1+1) row --> a2 = "Y&...
0
votes
2answers
60 views
How to "succinctly" detect changed values in the PostgreSQL's upsert (on conflict) where clause while supporting null changes?
We maintain a large data warehouse implemented in PostgreSQL and python. One very common pattern we do is to make upserts, and log when things were updated. We have some unique key my_key and values, ...
0
votes
1answer
48 views
How do I tell if a long running update won't lock the database?
There is a update query I need to run on a table in production. The update would touch millions of rows and I would take a couple of minutes.
The database is Mysql.
I was wondering if the table would ...
0
votes
1answer
35 views
Is this atomic set and get in MariaDB / MySQL: `UPDATE t SET col = @my_var := col + 1 WHERE id = 123`?
I want to atomically update a single row and get its value.
I discovered today that MariaDB / MySQL has this additional way of setting user variables using the assignment operator :=, described e.g. ...
0
votes
2answers
70 views
How to UPDATE faster with GROUP BY?
I have a typical article and tag_map tables,
CREATE TABLE `articles` (
`ID` int(11) unsigned NOT NULL,
`Tags` tinyint(3) unsigned DEFAULT NULL,
`Title` varchar(512) DEFAULT NULL,
PRIMARY KEY (`...
5
votes
2answers
742 views
PostgreSQL update join vs SQL Server update join
I recently started converting a personal project from Microsoft SQL Server to PostgreSQL and I was surprised at the abysmal performance I encountered doing an UPDATE JOIN between two tables.
Suppose ...
1
vote
2answers
262 views
ON DUPLICATE KEY UPDATE in INSERT INTO SELECT with GROUP BY
I have the query of
INSERT IGNORE INTO t2 (ID,P1,P2,Items)
SELECT ID,P1,P2,SUM(Items)
FROM t1 GROUP BY ID,P1,P2;
Since I need to run this query frequently to inspect changes in t1, I need ...
1
vote
0answers
78 views
SQL Server UPDATE Command Fails without error
Using SSMS running the below UPDATE command I get returned message says 1 row affected. but if I then follow it up with a Select and it shows that the row never updated.
This issue happens in SSMS and ...
0
votes
1answer
35 views
MS SQL Creating Tables and Adding Values by a Trigger
Table_A is an existing table which has 3 columns Id (PK), Name, Stock.
I would like to create a trigger to create a table.
For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10.
By ...
1
vote
3answers
259 views
Tsql help - doing a large update from CSV file to a table in ssms
I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from ...
0
votes
3answers
50 views
Postgres how to update first row in the child table for all parent entries
I have a table parent table workflow and child table workflow_task having one to many mapping.
workflow`
workID
workTitle
1800
vpInv1231
1801
vpInv1231
workflow_task
id
task_Type
workflow_id(fk)
...
0
votes
1answer
25 views
Updating an existing SQL Database with thousands of records
I am having some trouble thinking of how to update an existing table. There are currently 1500 items in it and the structure of the table is like this:
Old:
ID
Manager ID
Manager Name
Director
...
6
votes
3answers
787 views
Does a SQL Server update nvarchar statement overwrite the same address on disk if the new value is the same size?
Is it possible to overwrite the same disk address in a T-SQL update statement? A use case would be preventing recovery of the original text on disk for security or anonymity reasons.
CREATE TABLE Test(...
1
vote
2answers
19 views
How to update a table from a query result of another table related to the first one?
Here is my attempt. What is the correct syntax to achieve this?
UPDATE table_a a
SET a.firstCreationTime = c.created_at
FROM (
SELECT created_at
FROM table_b b
WHERE a.id = b.id
ORDER ...
0
votes
1answer
83 views
MariaDB why do I get error "Unknown table x in field list" when it does exist (yes, another one!)
Using mariadb 10.4.13-MariaDB but I get the same error in a copy of the database running under 10.3.
The important part of the schema shows
/*Database: mamlocal*/
----------------------
/*Table ...
0
votes
1answer
19 views
Update using PK from a lookup table, should I use the PK directly or select it using the associated lookup value? - best practice?
This is not asking for an opinion, simply what DBA's who are more professional than me tend to do in this situation.
Assume I have a simple lookup table status_type such as this, where status_id is ...
2
votes
1answer
43 views
How to code something like 'Update on duplicate key update to something else'?
As part of dealing with the UK's GDPA 'right to be forgotten' in a small database I want to update the (autoinc) PK of a row in table 'member' to an obfuscated value and let it cascade down through ...
0
votes
2answers
17 views
PolyLang WP Update to replace one string to another
I have to replace one string to another one in post_content column in table wp_post ( Wordpress ).
My query works only then when I use SELECT but when I want to use UPDATE command with REPLACE then I ...
0
votes
0answers
23 views
Replace or Update a Table Row - based on the presence of a matching row item
Unfortunately I know next to nothing about php coding that contains some sql code. The examples I have seen online do not seem to match the same syntax that I inherited with this code, which is making ...
1
vote
2answers
504 views
"1292 Incorrect datetime value" when updating an "empty" datetime field
I have an updated_at field that was not initially set with default datetime value on the server. For a short period, a bunch of records got inserted with an "empty" value and are showing up ...
10
votes
1answer
627 views
UPDATE on big table in PostgreSQL randomly takes too long
I'm trying to figure out why an UPDATE statement takes too long (>30 sec).
This is random, i.e. in most cases it finishes in under 100msec. However, sometimes (randomly) it takes > 30sec to ...
1
vote
1answer
39 views
UPDATE random rows
I'm working on creating a more efficient way to UPDATE random rows in a ticket allocation website we have.
Currently, we pre populate the tickets table with ticket numbers in subsequent order, i.e 1,2,...
0
votes
0answers
81 views
UPDATE with UUID() sets all rows with the same value
Having a table t1 with the field f1 BINARY(16) the following command updates all rows with the same uuid value.
UPDATE t1 SET f1 = UNHEX(REPLACE(UUID(), '-', ''));
OK, somehow I must have totally ...
2
votes
4answers
287 views
Philosophy on stored procedures that perform updates: is a 'granular' update approach sufficient?
I am embarking on on a large project and have an inquiry about stored procedures in general.
Let's say I have a User table.
UserKey IDENTITY NOT NULL PRIMARY KEY
FirstName nvarchar(50)NOT NULL
...
