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
2 answers
39 views

Trying to renumber a field in order grouping by another

So I have a table that has a column labeled 'sequence', it is not in fact a sequence data type, just a numeric field that translates in the object model to show items in a certain order. In a handful ...
Jonathan Beck's user avatar
0 votes
2 answers
35 views

Update multiple identical dbs at the same time

We have a software package where the db are running sql 2022. I have many regions. Subdomains for each company that we work with. However the software and dbs are identical in structure. Is there a ...
Corey W's user avatar
0 votes
1 answer
28 views

Before update trigger not triggered when change comes via logical replication

I have a test scenario of two postgresql databases connected via logical replication. DB "A" is publishing inserts and updates for table "a1" and DB "B" is subscribed and ...
Roberto Iglesias's user avatar
0 votes
1 answer
38 views

After Insert and Update Trigger on the same table

I have the following table in MySQL: CREATE TABLE `userlogtype` ( `UserLogTypeID` int NOT NULL AUTO_INCREMENT, `UserLogType` varchar(150) DEFAULT NULL, `InsertUser` varchar(100) DEFAULT NULL, `...
Steve Miller's user avatar
0 votes
1 answer
43 views

Update selected columns containing 0 with NULL

I want to update all columns named with suffix '[]' in the table geochem if they have zeros (0) with NULL. This query returns all relevant column names: select c.COLUMN_NAME from information_schema....
kitchenprinzessin's user avatar
-2 votes
2 answers
58 views

Replace "INSERT" by "UPDATE"

I'm working with some tables in an SQL-Server database, and I'm doing some crucial modifications. In order not to loose everything when I mess up, I decided to do a "Generate script" (data ...
Dominique's user avatar
  • 521
0 votes
1 answer
28 views

MySql TIMESTAMPDIFF in Update fails

I'm using MariaDB 10.4.25 and am trying to update some data in a WordPress table, but getting an error. I can run the SELECT query without a problem, but then when I run the UPDATE, it throws a "...
VBStarr's user avatar
  • 109
0 votes
1 answer
53 views

How to update a Postgres row atomically, with a computed value that depends on the new values

I have the following table in Postgres: CREATE TABLE avg ( user_id INT, numer DOUBLE PRECISION, denom DOUBLE PRECISION, frac DOUBLE PRECISION ); I want to accomplish the following, ...
Luke Hutchison's user avatar
0 votes
0 answers
41 views

How to replace (drop and rename) a table without triggering constraints in postgres

I have a table (A) that I need to reprocess from time to time. Doing that using update or upsert is really slow, so, to make things faster, I decided to create a new table (B), copy all the rows from ...
Sassa's user avatar
  • 135
0 votes
1 answer
48 views

Move row of a table to an inherited table in PostgreSQL

I have tables created as such: BEGIN; CREATE TABLE tag ( id SERIAL, name VARCHAR(255) UNIQUE NOT NULL, description VARCHAR(511) NOT NULL DEFAULT '', PRIMARY KEY(id) )...
SK19's user avatar
  • 101
2 votes
1 answer
50 views

Can I update multiple columns at once with multiple conditionals?

Having the following table snippet: CREATE TABLE `some_table` ( `exp_col_1` TINYINT(1) NOT NULL DEFAULT '0', `mark_col_1_as_exported` TINYINT(1) DEFAULT '0', `exp_col_2` TINYINT(1) DEFAULT ...
ReynierPM's user avatar
  • 1,764
0 votes
1 answer
59 views

How to use sp_execute_external_script to fetch USD Rate from an API and update a table?

I have this code that gives this error -- Set the API URL SET @url = 'https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF43718/dato/oportuno?token=...
Luis Avalos's user avatar
0 votes
2 answers
95 views

PostgreSQL - 30000 row updates, setting non-indexed boolean column to 'true' takes hours

I am running PostgreSQL 13, and single UPDATE statement that seems like it would be trivial to complete can take hours (at least in one case more than 24 hours). Selecting the rows with a SELECT ...
Justin Lowen's user avatar
0 votes
2 answers
98 views

Improve MySQL concurrent updates

Are there any suggestions to run the concurrent updates on a table (100+ concurrent updates on a specific row)? Same row, like updating the clicks count for an user. UPDATE `tbl` SET `cli`=`cli`+ 1,`...
TheDataGuy's user avatar
  • 1,930
0 votes
0 answers
30 views

Postgresql update column with window function

Take a look on example: https://www.db-fiddle.com/f/wN8A7pfuGos9KfLokFS3uy/0 It is possible to update payment_line_tab and paid column in efficient way? I would like to set paid column to value: 0 - ...
Robert's user avatar
  • 141
0 votes
0 answers
54 views

Mariadb Triggers after multiple insert, update one table one one time

CREATE TRIGGER after_composers_insert AFTER INSERT ON composers FOR EACH ROW UPDATE listings SET composer_name = NEW.composer_name WHERE composer_id = NEW.composer_id; CREATE TRIGGER ...
Alain Lagune's user avatar
1 vote
3 answers
56 views

MySQL query optimisation

This is the MySQL table DDL: CREATE TABLE `users` ( `username` varchar(250) NOT NULL, `jid` varchar(250) NOT NULL, `nick` text NOT NULL, `subscription` char(1) NOT NULL, `ask` char(1) NOT ...
datascinalyst's user avatar
-1 votes
1 answer
95 views

Replacing substrings in array elements

I have a table with an array of URLs in column urls. Now there's a need to update some URLs changing the hostname. E.g.: {https://storage.host1.com/file.jpg, https://storage.host1.com/file2.jpg} ...
minicooper's user avatar
0 votes
1 answer
49 views

Query for converting a column of numbers matching a regex in MySQL

I have a column which contains string values of the format e.g AB12345678 - two letters followed by 8 numeric digits. I need to write a MySQL query to insert a 5 before each set of 4 digits thus ...
therobyouknow's user avatar
0 votes
1 answer
48 views

Is UPDATE FROM still not in the ANSI standard?

This article fascinated me it claims that FROM clauses in UPDATE are not part of the ANSI standard. Unfortunately, it is very old and I don't care to buy a copy of the latest ANSI standard for SQL. Is ...
J. Mini's user avatar
  • 123
0 votes
1 answer
167 views

UPDATE table vs JOIN performance

I currently have a query with roughly 10 JOIN statements that takes about 10 seconds to run. I have this displayed as a report on SSRS. Would it be better to create a table in SQL server and run ...
Bigbob23's user avatar
  • 121
0 votes
1 answer
118 views

Batch update in a large dataset

We have 3 tables: Product (product) Stock (product_Stock) Price (product_price) In each table, we have more than 290 bilions of rows. These tables have a compound primary key: product_fk and ...
Gadonski's user avatar
  • 191
0 votes
1 answer
132 views

PostgreSQL UPDATE successfully executed but stopped making changes

I made some UPDATE-s to my PostgreSQL DB, and after some point, they all of a sudden stopped making changes, even though the result says that there were updates made. I tried doing it inside ...
user2340939's user avatar
2 votes
0 answers
58 views

How to swap the paths of two records (and their children) using a postgresql ltree?

How do I swap two record's paths/positions in an ltree using postgres, where all children follow? There is a uniqueness constraint on the path. Given the following records on a pages table: id: 1, ...
Matthew's user avatar
  • 121
0 votes
0 answers
26 views

Update parent table, based on one record from children table (one-to-many)

I have a Persons table, which has a DefaultEamil column. I also have an Emails table which has PersonId, Value, IsDefault columns. A person can not have multiple default emails. There is an index ...
Saeed Neamati's user avatar
2 votes
1 answer
153 views

MariaDB 11.0 to 11.1 GA fails with ERROR 1210 (HY000) at line 32: Incorrect arguments to DATA DIRECTORY

I upgraded to MariaDB 11 some time ago, and all has been working well for a long time. I saw there was a newer GA release, 11.1, so decided to update to that, but the upgrade fails. This is on Windows ...
user946207's user avatar
0 votes
1 answer
40 views

Sum column and remove lines sum

I want to sum the "amount" column grouped by name and server, and remove duplicate lines. I want those changes are saved (not only with one query). For example, here is begin data: Id ...
Elikill58's user avatar
  • 223
2 votes
1 answer
51 views

PostgreSQL: Update multiple data while using the binding variable

I create the new table and insert 10 records into the new table as below: userdb=> CREATE TABLE test_tbl(ID INT PRIMARY KEY, STR VARCHAR); CREATE TABLE userdb=> do $$ begin for r in 1..10 loop ...
Alvin Kam's user avatar
0 votes
1 answer
109 views

UPDATE & SELECT & INNER JOIN combined (SQL92) — How?

I have two tables with the following columns: Table1 id Sort dou and Table2 id_Table1 Sort and I need to read all values of Table1.Sort where Table1.dou = 1 and write them into Table2.Sort, into ...
Gary U.U. Unixuser's user avatar
0 votes
1 answer
193 views

Using OUTPUT INSERTED.id to UPDATE (i.e. not insert new record) existing rows

1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values: [a,b,Null], [c,d,Null], [e,f,Null] 2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having ...
M S's user avatar
  • 21
0 votes
1 answer
67 views

Should I worry about a serial update execution plan?

For a mature .Net project I support we're currently exploring options to remove Entity Framework Core from the project while still maintaining some of the EF functionality we rely on. At the top of ...
Adrian's user avatar
  • 103
1 vote
0 answers
23 views

Strange performance difference in update query alternatives in mariadb

We have a table with composite primary key with 1.6M rows where 2 different ways of writing the same query yields vastly different response times. Unfortunately our ORM creates the slow quiry variant. ...
Medisch Labo Bruyland's user avatar
0 votes
1 answer
91 views

How exactly does SQL Server execute an UPDATE statement?

Below two-sessions-trying-to-update-the-same-row scenario was provided by Paul White. Scenario 1: Session 1 obtains a U lock on the base table row while reading. Session 2 blocks, waiting to acquire ...
Fajela Tajkiya's user avatar
0 votes
0 answers
48 views

Postgres 13: Update column when MV refresh is complete

I have what feels like a very simple operation I wish to perform, but cannot find any guidance. I have an application that allows users to upload a series of files with records, which get processed ...
Kevin H's user avatar
0 votes
2 answers
97 views

On update: Login failed for SQL Server authenticated sysadm

I run into a weird bug? User complained to me that he cannot anymore update certain tables in database. He had owner rights in database and the login was window login (ad-login). I tried elevating him ...
user1054844's user avatar
-1 votes
1 answer
42 views

How do I determine the flow of updates in database?

I am pretty sure I can't make this clear enough, but I'll try my best. Say there are tables in database. Players_Basic_Info Players_Login Players_Net_Worth Players_Salary Players_Matches_History ...
achhainsan's user avatar
0 votes
0 answers
31 views

Finde the difference in UPDATE in PostgreSQL

I would like to determine the difference in an UPDATE that was carried out on a table DE_projects. So between old and new values. The aim is to monitor changes in the permissions column in the ...
MTwain's user avatar
  • 1
0 votes
1 answer
42 views

Compositing ordered SQL tables without Joins

I'm currently exploring some weird ways of doing things and was wondering if anyone has any experience with the following methodology. Background I have a handful of NF3 tables, and NF1 source data. ...
Vocoder's user avatar
  • 117
0 votes
1 answer
182 views

"ON UPDATE" in self referencing table

I have a table Category with a parent_id column referencing the self id column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too. At ...
bguernouti's user avatar
1 vote
2 answers
102 views

How to leave only the rows with an timestamp difference with an interval greater than a parameter

I have a Postgres table with timestamps as entries. From this table, I would like to calculate a new one where there are no consecutive entries with a timestamp difference shorter than 400 ...
eddie_jung's user avatar
1 vote
1 answer
28 views

update row with age calculation with DOB in related table

SQLite database with a table "events" with a columns events.eventDate, events.ageAtEvent and events.foreignKey_participants, and a table "participants" with columns participants....
Stam Kapetanakis's user avatar
0 votes
0 answers
129 views

Query update issue, it updates only the first match sqlserver2012

I'm trying to transfer part of data "ID" from table1 to table2, you may ask why doesn't table2 have the all the data. Answer is simple the data is generated by a program which doesn't ...
Peter's user avatar
  • 79
0 votes
0 answers
259 views

How to debug this tuple await events in RDS PostgreSQL

I have been noticing that sometimes we get a lot of open connections for a long time. Enabling enhanced metrics in RDS PostgreSQL I saw the following: It seems this tuple wait event is making a lot ...
Antonio Gamiz Delgado's user avatar
0 votes
2 answers
1k views

Transactions are not completing when locking and updating one row at a time

I have a database function I have written to atomize a simple table update. My system is status driven, so I want the next row with the desired status, and update it to a new status, and return the ID....
Sherman's user avatar
1 vote
2 answers
429 views

How to update each value in an array with a function?

I want to trim spaces from each value in a text[]. If this was just a text column I could do: update server set tags = rtrim(ltrim(tags)); However how do I do this for an array? As otherwise that ...
Chris Stryczynski's user avatar
0 votes
1 answer
175 views

Modifying and RETURNING data in the same postgres command

I have a simple bit of SQL to reset some values in a table UPDATE tableA SET field1 = 0 WHERE field2 IN (SELECT .......) This works fine, but I'd like to modify it to return the value of all field1 ...
ConanTheGerbil's user avatar
-1 votes
1 answer
37 views

Postgres linking two tables

i am trying to set up a geo data base for my company using PostgreSQL with pgAdmin4. I am very new in database administration. We need a database storing the information about drilling points. Each ...
Anna's user avatar
  • 1
-1 votes
2 answers
407 views

Cost of an internal update on 100k records

I'm curious about an update that I'm planning to do in pgsql and cannot find any topic about this case. I have to do an internal update on a database with 150k records UPDATE "table" SET ...
Luc Delmon's user avatar
1 vote
0 answers
212 views

update trigger after insert on same table

So, I'm supposed to make a trigger that applies md5 to a row after an INSERT was made on it. Here is what I came up with: delimiter $$\ create trigger pd_encrip before insert on usuario\ for each row\ ...
Jade Fernandes's user avatar
1 vote
2 answers
120 views

How to improve performance on query with nested INs?

I am trying to improve the performance of this query using joins instead of INs. Also I am using the GUI tablePlus with Postgres. What are some good tools to assist with performance? UPDATE app_public....
Nate K8's user avatar
  • 25

1
2 3 4 5
21