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.
945
questions
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.
...
- 123
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 ...
- 101
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
...
- 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 ...
- 801
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 ...
- 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....
- 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 ...
- 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
...
- 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 ...
- 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 ...
- 33
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 ...
- 101
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....
- 113
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 ...
- 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. ...
- 31
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 ...
- 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='&...
- 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 ...
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 ...
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 ...
- 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:...
- 3
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 |
+----------+--------------+------------------...
- 15
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 | ...
- 73
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) ...
- 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 ...
- 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 ...
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 ...
- 25
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 ...
- 13
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&...
- 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](
...
- 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,
...
- 13
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 ...
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, ...
- 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 ...
- 229
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 ...
- 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 ...
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 ...
- 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 ...
- 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 ...
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://...
- 227
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 ...
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 ...
- 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 ...
- 23
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-...
- 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 ...
- 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
...
- 13
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 |
--------+-----------------------------------------------...
- 1
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
(
...
- 41
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, ...
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|
|---------------|---------------|--...
- 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 ...
- 107