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
2 votes
1 answer
47 views

Understanding `WHERE x = NEW.x` Syntax

I'm writing an application that uses SQLite to store court information obtained from a government database in a client table. I've set up a DB Fiddle here, which is a simplification of my application. ...
user avatar
0 votes
4 answers
49 views

Practical way of finding last update/insert/delete of any table in a database?

OS: W10. This question is specifically about what's possible in MariaDB, specially version "Ver 10.16 Distrib 10.2.11-MariaDB". Edit In light of the answer by Rick James I have now upgraded ...
user avatar
2 votes
2 answers
152 views

how to update one column with trigger mysql

i have 2 tables the first one is Table KEYS game keys WOW AAAAAAA-AAAAAAA-AAAAA WOW BBBBBBB-BBBBBBB-BBBBB L4D2 AAAAAAA-AAAAAAA-AAAAA TABLE inventory game country language pieces WOW USA EN 0 ...
user avatar
  • 31
2 votes
2 answers
166 views

Writing a query that will quit if there's a lock on a target row

Is it possible to write an UPDATE query that will simply quit if the record it tries to change is locked by another process (rather than waiting for the lock to be released)? I have a process that ...
user avatar
5 votes
1 answer
241 views

Does UPDATE write a new row version for unchanged, TOASTed values?

I am working with a PostgreSQL table with a large TEXT field, which is theoretically updated on a regular basis. I have thought about storing the data directly in the filesystem, but with TOAST, the ...
user avatar
  • 153
3 votes
3 answers
71 views

Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?

In Postgres 13, I have a table which gets updated frequently. However, the update query is rather complicated and uses the same values multiple times. So, using a CTE seems quite a logical thing to do....
user avatar
  • 289
2 votes
1 answer
77 views

Assign ids from a table to records of another table in PostgreSQL

Starting from a table datos, in a PostgreSQL database, that holds 9+ M data of bird species presence in Spain, I created a new table extracting all different locations from that table using create ...
user avatar
  • 205
-2 votes
1 answer
36 views

split a row value according to a specific column

I have total value in table A and need to get row value in table B until reached value in table A. Table A ID Available 1 99 2 0 Table B ID Rack RequiredQty 1 A 60 1 B 102 1 C 8 2 A 10 ...
user avatar
  • 1
0 votes
1 answer
33 views

Returning in query whether a column has been changed by the current query or not

I have a query like UPDATE my_table SET my_value = CASE WHEN random() > 0.5 THEN my_value * 2 ELSE my_value END RETURNING *; Now, inside the RETURNING statement I'd like to have a boolean ...
user avatar
  • 289
2 votes
1 answer
55 views

Do UPDATEs block SELECTs?

I need to query a frequently updated record, does it wait until all updates finish? /updateUser/1 Many requests are sent UPDATE users SET count = count + 1 WHERE id = 1 LIMIT 1 /getUser/1 While the ...
user avatar
0 votes
1 answer
101 views

How to handle possible NULL in a Trigger Updating a table without a Primary Key

I'm using Postgres Triggers to keep two tables in sync for a short term migration. However, I have one join table that doesn't have a primary key, but two columns that function as a composite primary ...
user avatar
1 vote
0 answers
481 views

UPSERT multiple rows, do ON CONFLICT DO UPDATE SET conditional check/update row-wise

I'm trying to UPSERT multiple rows into a Postgres table (ie. INSERT ON CONFLICT DO UPDATE SET multiple rows). But I want the ON CONFLICT DO UPDATE SET conditional check/update do be done row-wise (ie....
user avatar
0 votes
0 answers
20 views

Update Row --- get rid of row lock for perfomance? (postgres)

I need to update table A with the earliest "event" from Table B (event_time), matching on an ID. A might have 1000 ids ... and B has a bunch of foreign ids and linked events. So each day, a ...
user avatar
  • 1,493
3 votes
0 answers
29 views

Does MySQL always run foreign key constraint checks when a row is updated? Or only when a relevant column is changed?

I have this question and I haven't found specific documentation to confirm the behavior and am unaware of how to manually check this myself. Consider I have table A with b_id foreign key to table B. ...
user avatar
0 votes
0 answers
42 views

Can't UPDATE a database table

I'm getting a 'Error synchronizing data with database' error on MySQL 5.6.10 (on AWS). Trying to update a field (using dBeaver) but getting error message/ Further: Data truncated for column ...
user avatar
  • 1
0 votes
0 answers
21 views

Is there a purpose for UPDATE ... WHERE but with same values?

I am translating some code from PHP to Python, and I came upon this interesting snippet: $sql11 = "UPDATE tbl_pas SET api_id_pas='" . $api['kd_pas'] . "' WHERE api_id_pas='&...
user avatar
  • 187
0 votes
1 answer
44 views

How do I fix this SQL server query 2008r2

I'm trying to update type from table Titem column type and set it to new id, while having it to check if user id owns those 2 items(oldid which we are updating, 2nd id which is just for checking if ...
user avatar
0 votes
2 answers
104 views

Is updating a table by delete+insert better than merging with a temp table?

I have two tables in Postgres. The first table has 120 columns (stock metrics like returns, sales etc.), and the second table has 5 columns with a one-to-many relationship and on cascade delete ...
user avatar
0 votes
1 answer
236 views

How to check if update query was executed or not in MySQL?

I have an update query in MySQL and I want to return true or false based on whether my update query was executed or not. Is there any way we can find whether the update query is executed? UPDATE table ...
user avatar
  • 3
0 votes
1 answer
52 views

update datatype field int to decimal128 in mongo db in nested json structure

Hi have tried to updated nested field datatype db.test.find().forEach(function(data){ data.age = NumberDecimal(data.age); data.phone.$.mob = NumberDecimal(data.phone.$.mob); db.test.updateOne({_id:...
user avatar
1 vote
2 answers
224 views

MySQL - Update column based on multiple conditions

I have a table which has Rows like these: +----------+--------------+---------------------+------+ | CASE_ID | CAT | BIRTH | TYPE | +----------+--------------+------------------...
user avatar
0 votes
0 answers
177 views

Update row which depend on previous row value MySQL

I have a table where some column value depend on previous column value. Here is table sample data. +----+--------+----------+--------------+--------------+ | ID | Demand | Arriaval | OpeningStock | ...
user avatar
0 votes
1 answer
35 views

UPDATE a table by self JOIN and GROUP BY

I have a table of team players in which the PlayerID is missing in some rows. CREATE TABLE TeamPlayers ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, TeamID int(11) unsigned NOT NULL, PlayerID int(11) ...
user avatar
  • 4,169
0 votes
1 answer
66 views

Efficient update with join [closed]

I have a table with ~10,000 rows that looks like: main ( external_id varchar, int_col int, varchar_col varchar, date_col datetime2, text_col text, -- and 50 other ...
user avatar
  • 411
0 votes
1 answer
32 views

UPDATE table IF data in another

I would like to retrieve some data from another table and put it in my first table. I used this query: UPDATE table_1 SET table_1.firstname = ( SELECT firstname FROM table_2 WHERE ...
user avatar
0 votes
1 answer
72 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 ...
user avatar
1 vote
1 answer
149 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 ...
user avatar
0 votes
1 answer
30 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&...
user avatar
  • 103
1 vote
2 answers
343 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]( ...
user avatar
  • 287
0 votes
1 answer
32 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, ...
user avatar
0 votes
1 answer
240 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 ...
user avatar
4 votes
1 answer
404 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, ...
user avatar
  • 55
5 votes
1 answer
371 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 ...
user avatar
0 votes
2 answers
88 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 ...
user avatar
  • 1
0 votes
0 answers
24 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 ...
user avatar
0 votes
0 answers
38 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 ...
user avatar
  • 239
1 vote
1 answer
86 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 ...
user avatar
  • 11
0 votes
2 answers
40 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 ...
user avatar
0 votes
0 answers
30 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://...
user avatar
0 votes
1 answer
37 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 ...
user avatar
0 votes
1 answer
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 ...
user avatar
  • 101
2 votes
1 answer
375 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 ...
user avatar
0 votes
1 answer
73 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-...
user avatar
  • 283
0 votes
0 answers
33 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 ...
user avatar
  • 101
1 vote
1 answer
44 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 ...
user avatar
0 votes
0 answers
24 views

Update a field of type jsonb[] - postgres

Dear Awesome community, I have a table as follow: id | keys | typeid | sectorid | commitid | --------+-----------------------------------------------...
user avatar
0 votes
4 answers
226 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 ( ...
user avatar
1 vote
1 answer
60 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, ...
user avatar
0 votes
1 answer
39 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| |---------------|---------------|--...
user avatar
  • 101
-1 votes
2 answers
182 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 ...
user avatar
  • 107

1
2 3 4 5
19