12,269 questions
Best practices
0
votes
7
replies
43
views
Create a trigger with clause to update only one specific row
Due to a BUG, an application that persists data in an Oracle 19c database is updating a incorrect value in a specific row of a table.
This incorrect value prevents users from accessing that table ...
- 1
-2
votes
1
answer
166
views
Updating a table with CASE
In this example I wanted to replace every NULL value in the Quantity column with the mean of the column. I'm using CASE here to achieve that but what ends up happening is instead of replacing NULL ...
5
votes
4
answers
226
views
UPDATE with LEFT JOIN and condition IS NULL
I have the following table definitions:
Table public.messages:
Column
Type
Collation
Nullable
Default
ip
text
msg
text
ignore
boolean
Table public.host:
Column
Type
Collation
Nullable
Default
ip
text
...
-4
votes
1
answer
100
views
How do I retrieve the primary key of the updated record?
I have this insert query for my MariaDB 10.6.19 database:
CREATE TABLE manufacturers (
manufacturers_id int(11) NOT NULL AUTO_INCREMENT key,
manufacturers_name varchar(32) unique NOT NULL,
...
- 433
2
votes
2
answers
125
views
Replace/insert XML text node in XQuery
Consider the following code to update "/project/cost/final" in an untyped XML.
The code will run successfully, however, the value actually remains unchanged.
DECLARE @xml XML;
SET @xml = '
&...
- 61
2
votes
2
answers
139
views
Can't specify target table for update in FROM clause
UPDATE
`details_audit` a
SET
a.count_changes_below_100 = (
SELECT
COUNT(*)
FROM
`details_audit`
WHERE
sort_id < 100
);
This worked on a previous version of ...
- 129
5
votes
2
answers
131
views
Update MySQL database column to round all values to 2 decimal places
I have a MySQL database of some 5000 products imported from Excel a while ago. Unfortunately, Excel truncates values with trailing zeroes so they were imported into the Price column in a mix of values ...
- 157
-1
votes
1
answer
167
views
How to update from a temporary table? FROM clause not working
I'm trying to update table1.logged_off (a LONGDATE column) using data from a local temporary table with matching IDs. Here's what I have so far:
Here's what I'm doing:
Step 1: Create and fill the ...
- 284
2
votes
2
answers
90
views
Apply a conditional rank to a temporary table
Here's an idea of what the temporary table contains:
ID
FullRank
Special
1
1
No
1
2
Yes
1
3
No
2
1
Yes
2
2
No
3
1
No
3
2
Yes
3
3
Yes
3
4
No
3
5
No
And this is what I'm trying to achieve...
ID
FullRank
...
- 21
-1
votes
2
answers
177
views
Insert or Update - Understanding difference between SQL statements [duplicate]
We have a database utility in our product suite that handles both the creation of new databases and the upgrade of existing ones.
For new tenants or customers, the creation flow is executed.
For ...
- 2,595
0
votes
2
answers
97
views
does an update need to rewrite the entire row in mysql?
I have this table:
id, heartbeat_at, data_json, ...
The column heartbeat_at is frequently updated with this query:
UPDATE table SET heartbeat_at = NOW() WHERE id IN (...)
The data_json column is much ...
- 14.4k
0
votes
1
answer
94
views
UPDATE with JOIN does not seem to work correctly
I have two table in a Postgres database:
Table users_ill contains users that are ill.
Table admin_employees is for admins that have users by the department_id so that an admin can only see users ...
- 291
1
vote
2
answers
98
views
Query to automatically update values based on other certain criteria [closed]
I've got a SQL query that is trying to update threshold information on a SolarWinds database.
The main data is within the Nodes view and the Thresholds table contains the warning and critical ...
1
vote
0
answers
77
views
Method to automatically update MariaDB SQL database based on changes to Excel file
Basically, I have a MariaDB database (hosted through XAMPP). Some of the tables in the database are based on Excel files kept various places in my organization's main drive.
IDEALLY: if any of those ...
- 514
-1
votes
1
answer
53
views
Replace or update values in a field that are incorrect
I have a table that looks like this:
id|week|start_date
1,4, 0026-10-23T00:00:00.000+00:00
2,5, 0026-09-22T00:00:00.000+00:00
3,6, 0026-09-25T00:00:00.000+00:00
I am trying to write a SQL statement ...
- 352
4
votes
5
answers
156
views
Cannot update postgres table from cte, value goes missing
I have a Postgres Timescaledb table (mysolar) in which I collect energyvalues (time,energy) from my solarpanels. Sometimes, for whatever reason, I get a ‘zero-value’ reading. (see example table). I ...
- 61
2
votes
1
answer
114
views
After Collation change not being able to update values (MariaDB and MySQL)
I tried to update my tables collation from utf8mb4_general_ci to utf8mb4_unicode_ci, which is pretty simple specially using pmpMyAdmin.
But after several attempts to update values it seems that ...
- 41
1
vote
0
answers
45
views
Is there a way to do something like an update statement using an Excel spreadsheet via SSIS? [duplicate]
I am trying to update certain data in a table that I would typically update using an update statement but the problem is that the data will come from an Excel spreadsheet.
I know I can create a table ...
2
votes
2
answers
100
views
Is it possible to query the state of an updated table in postgresql within the same query?
I want to update a table in a WITH query (not a requirement, if it's achievable another way that's fine too) and count rows of the updated table (including the updated row) for the subsequent query.
...
- 10.4k
1
vote
0
answers
45
views
How to create trigger after update of record on table 1 then insert of that record into table 2?
I want to create trigger in Oracle 12c DB which will do insert in table_2 when update of record in table_1. Both table_1 and table_2 are same structure. When user set value of email_sent to 'Y' then ...
- 11
-1
votes
1
answer
87
views
How to insert different values in a newly added column in a single update statement?
In t-SQL, I have added a new column named "AGE" to table "employees", but I have to write different update statements for insert each age value, since each employee has a different ...
0
votes
3
answers
104
views
Strange behavior of Update Statement
The thing is about a very simple update statement:
UPDATE `user` SET fans = fans +1 where id = 8;
(because it will not cause any concurrency issue when just single[auto commit] use it, so I want to ...
- 81
0
votes
1
answer
84
views
Update a date field in selected records with a calculated, relative date
The end result I am looking for is to have a query that will find specific records in Table1, use a field in joined Table2 to retrieve a day of the week, use a formula to determine the next occurring ...
3
votes
1
answer
91
views
MariaDB UPDATE with Subquery Behaves as If SET Clauses Are Evaluated Simultaneously
I encountered an unexpected behavior in MariaDB when performing an UPDATE statement with multiple SET clauses. Here's the setup:
Table Creation and Initial Data
CREATE OR REPLACE TABLE products (
...
- 83
0
votes
1
answer
45
views
SQLite: NOT GLOB returns the string I specifically want to exclude
I am attempting to do a column update in SQLite where if a certain string exists, update the column EXCEPT if it matches a particular string. Note, I can't use an EXCEPT statement in the select query ...
- 681
0
votes
0
answers
644
views
Spring error "Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)" when I try to update
In spring boot with Java I have an Entity class that contains a list of elements. I want to create an endpoint (POST? or PATCH?) from which I can add a new element to those present:
import java.time....
- 8,058
1
vote
1
answer
39
views
Problem updating integer field in Supabase from kotlin APP
I'm trying to update an int4 field on supabase, but nothing happens when the code runs. The updates in the when block for OnCallDates, GymCallDates and LeaveDates work perfectly, even in the else ...
1
vote
1
answer
43
views
How to fix error after using trigger in Oracle
I created trigger which should insert record into T_TABLE_B
after T_TABLE_A changed(update) one column. But after updating T_TABLE_A, got error
ORA-04091: table T_TABLE_A is mutating, trigger/function ...
- 11
0
votes
3
answers
271
views
Update Object_construct nested in an Array_construct in Snowflake
Can anyone please help me with this scenario where I have might have multiple OBJECT_CONSTRUCT nested within an ARRAY_CONSTRUCT. I am not able to update one value of an element within it. I am using ...
- 7
1
vote
1
answer
116
views
Format SQL query output into a report text structure for LLM use
Question:
I have a query that lists the following columns: repid, Car, Model, Fault, and cnt (a count of occurrences). The current output of my query looks something like this:
Query:
SELECT repid, ...
- 8,170
0
votes
1
answer
57
views
Update query by order of sequence
I am having an issue trying to use an update query:
select
lc.name as locations,
lc.sequence
from
dbo.LocationContainers lc
Currently this query shows this result:
locations ...
1
vote
0
answers
52
views
JPA EclipseLink: UPDATE Not Executed After Committing Merges
Using EclipseLink 2.6.4 with JTA and the property eclipselink.persistence-context.flush-mode=commit.
The UPDATE in the database is not done when I'm trying to commit a transaction where several merges ...
- 21
0
votes
0
answers
62
views
Update join effect not visible in first run of select
I have this very strange behavior using pglite (PostgreSQL version 16) in Firefox, where I run an UPDATE JOIN, and then a SELECT which the first time around doesn't reflect the change made by the ...
- 10.8k
-1
votes
1
answer
72
views
How to keep existing value (append) while using SqlParameter to update a record
I want to update a column in SQL Server by keeping its original value and appending new value separated by comma. It can be done like this:
string sql = "UPDATE Table SET Column = Column + ', ...
- 3,477
0
votes
1
answer
128
views
SQL updating ID column in multiple tables with new values
I have let's say 20 tables, which have chain_id column with values.
We have decided to change all chain_id values to new ones (it's not PK/FK). I have a mapping table with chain_id and new_chain_id.
...
- 71
0
votes
1
answer
81
views
Rows inserted in Oracle procedure not available until after the procedure completes
I have a recursive procedure in a package in Oracle that traverses through a network. The recursive function runs down the network from the top and then returns values back up as the recursions close. ...
0
votes
2
answers
416
views
Update with limit and offset
Simplified scenario:
I want to update one table A (10 million rows) with a value from another table B (20 mil rows). The two tables are linked with ids.
It took more than 7hrs to update the whole ...
- 13
-2
votes
1
answer
56
views
Update consecutive dates for a certain amount of records
I have a MySQL 5.7 table.
There are 9000 records that I need to set consecutive dates to.
The first 300 records will have 2025-01-02.
The next 300 records will have 2025-01-03.
And so on until all ...
- 3,099
0
votes
0
answers
47
views
MS Access - Operation must use an updateable query [duplicate]
I'm trying to update 5 columns on a table using the results of another query. The SQL is below. T used the other query to selet the RO numbers I need with out any issue. I'm trying to join the ...
- 940
3
votes
3
answers
96
views
Update a table with a rank number with a subquery
I am using the following SQL query to update a temp table to be able to determine priority.
This is my SQL statement
UPDATE #CARD
SET rank_no = x.no_rank
FROM
(SELECT
rank_no, ...
0
votes
2
answers
128
views
How to speed up the tranfer of information between Excel and Access?
I am having problems with updating records in MS Access through Excel macro (coded in VBA).
I'll try to explain better what's the issue:
I created a UserForm to input the information inserted in ...
- 19
0
votes
0
answers
55
views
Why is ON CONFLICT DO UPDATE not working with PostgreSQL column aliasing?
I'm trying to perform an INSERT operation with an ON CONFLICT DO UPDATE clause in PostgreSQL. My table contains column names with aliases (e.g., "ITEM_CODE(material_code)"), and I want to ...
1
vote
1
answer
76
views
Update flag based on if values exist in another table for multiple columns
In Snowflake, I would like to update a flag in the current table if there is values matching in 4 columns in another table. Only one columns has to match to set it to True.
data looks like below in ...
- 441
0
votes
1
answer
274
views
DataStage Oracle Connector doesn't execute 'After SQL statement'
I have a problem with an Oracle Connector. It should work as follows:
1 - Delete rows where the start date is equal to SYSDATE
2 - Insert data in that table
3 - Update rows with start date different ...
- 17
2
votes
2
answers
94
views
Update values from a column of one table if they exist in another table's column if these values are unique
I have a column filled with values that need updating because they are too ambiguous and I want to update these values with values from another table columns.
Player Table:
ID
Email
FirstName
1
Bob....
- 117
1
vote
1
answer
63
views
Update Between Two Tables
I created the question in "Rich Text Mode", so might need to view in that to get the proper layout for the tables.
I have two tables.
Table1
UserID
TicketNumber
CaseID
Status
1
INC1234567
...
- 35
1
vote
0
answers
36
views
eloquent function delete & update not working
I use eloquent for delete in my project with laravel8.it returns true but the records not remove from table.
what I try to do:
Package::findOrFail($id)->delete();
I have this problem on eloquent ...
- 11
0
votes
1
answer
52
views
MySQL update large table with a variable in SET columname
I have to update a table with SET columname as variable selecting from another table.
This is my MySQL :
UPDATE famcharts_old.db1w_matrix
JOIN famcharts_old.tbl_dirtosv
ON famcharts_old....
0
votes
1
answer
80
views
Equivalent of SQL Server bulk insert for updates?
I've made extensive use of bulk insert for onboarding and similar tasks. Unless I'm mistaken, one of the main reasons this runs rapidly is that much of the work is done locally on the server as ...
- 9,344
-1
votes
1
answer
125
views
Syntax Error in Update Statement. This is what I got when I try to update my database [duplicate]
sql = "Update tblUserAccount set username = '" & txtUsername.Text & "', password = '" & txtPassword.Text & "', AccessLevel = '" & cmbAccessLevel.Text &...
