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
0
votes
1answer
12 views

How to implement insert, update, delete with a many-to-many relationship for tags?

I am creating an application to manage some ebooks and I would like to have tagging functionality. The best db design I have found is to be the many-to-many relationship. I know how to set up my ...
1
vote
1answer
32 views

Can I retry every SQL query in case of a deadlock exception (MSSQL)

Is it always safe to retry an SQL query in case of a deadlock exception? Can this lead to unexpected behavior or executing an operation twice? I think a deadlock exception always means a rollback even ...
0
votes
1answer
7 views

How can I update the value of a column for two rows that have a unique constraint in SQLite?

I have the following table CREATE TABLE IF NOT EXISTS node ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, type TEXT CHECK(type IN ("DIRECTORY", "EXECUTABLE", "DOCUMENT&...
1
vote
2answers
198 views

SQL Server how to upload big json into column, performance issue

I am experiencing poor performance on a single row insert/update statement over a table when a nvarchar(max) column has few MB of data. Here my table structure : CREATE TABLE [dbo].[tbl_set_Cart]( ...
0
votes
1answer
26 views

How i can use the function encrypt in one consult update

I'm trying to duplicate a column, but I would like to encrypt it with not very high data value, and I was able to do it, well, just the first part. CREATE TABLE probeta ( id int primary key, ...
0
votes
1answer
31 views

Division By 0 error when running an update query MySQL

I am trying to run an update in a table and I get an "Error Code: 1365. Division by 0". The sql commands I use are these: SET @@autocommit = 0; start transaction; update XE_General join ...
3
votes
1answer
355 views

Best practices for updating data on a "live" table

Scenario: I am running a user application hooked to a SQL Server as the data backend. During a normal day, the data in the table associated with the application can be subject to frequent change, ...
5
votes
1answer
349 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
49 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
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
31 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
47 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
34 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
22 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
135 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
19 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
195 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
33 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
74 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
30 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
157 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
23 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
62 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
73 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
68 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
46 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
71 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
1k 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
352 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
79 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
398 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
55 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
790 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
20 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
154 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
46 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
18 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
2 3 4 5
19