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.
764
questions
0
votes
0answers
21 views
Fill in Ending Balance from previous months balance
I need to fill in the Ending Balance of each Year / Period where it is 0 (zero) but a previous period for that year and company has an ending balance - basically carrying forward the ending balance ...
0
votes
1answer
16 views
how mongo db does locking for updates/inserts?
I have many data for each user that with write heavy and many for each separate users that there is not any relation between user's data; so there is two options for me to store them:
1. keep all user'...
0
votes
2answers
20 views
Select data from table join or UDPATE?
I have a question regarding PostgreSQL DB performance. I am given a CSV file with a lot of rows (let's say around 1 million rows) that represents updates to a database table (let's call it MY_TABLE). ...
2
votes
1answer
39 views
UPDATE column to MIN of a SELECT result set - what went wrong?
I've got the following scenario:
I've got
a table of assets with standard columns
a table of hardware assets with extension columns
a history table where all changes to an asset are stored with ...
0
votes
1answer
10 views
Update Mysql records using Same table but Based on same table record
I have table in which i want to update records based on it's 'wishlist_id' value.
Sample format of table records.
Expected table record after update.
So can you help to update this record using ...
1
vote
2answers
34 views
Update all values for given key nested in JSON array of objects
I have a jsonb column data in a Postgres table my_table. It contains the following data:
[
{"id":"1","status":"test status1","updatedAt":"1571145003"},
{"id":"2","status":"test status2","...
0
votes
2answers
46 views
Editing all columns
I have a db table with more than 200 rows (table name: ports).
There is this column (port name). The data in the column is like: aaa 1/15.
I want to edit all the data in this and turn the aaa 1/15 ...
0
votes
3answers
25 views
Update column with the value of two columns multiplied
I want to update ColumnA with the value of ColumnB x ColumnC. Can someone please help? I have tried the following:
UPDATE database.table
SET ColumnA = ColumnB * ColumnC;
Error message as follows:
...
0
votes
1answer
20 views
Update a column based on another's table column value
I have a table Users
+----------+----------+
| ID_EMP | isActive |
+----------+----------+
| 100 + 1 +
| 101 + 1 +
+----------+----------+
and a table UsersInfo
+----------...
2
votes
0answers
106 views
SQL Server 2016 SP2 CU9 - WFC Failover failing
I have updated my SQL Server 2016 on one of my Windows Failover Cluster nodes successfully. When I attempted to failover one of my SQL instances onto the newly patched node, SQL Server service fails ...
-2
votes
1answer
39 views
ON DELETE / ON UPDATE doubts
I was looking at database past exam from the school that I attend, and I have a question about ON DELETE and ON UPDATE.
The question in the past exam was:
Show the contents of the tables after ...
0
votes
2answers
23 views
Populate columns of table with other table that doesn't have relationship
Hello I have 2 tables in PostgreSQL: the 1st one
"Products"
id
price
factor1
factor2
the columns factor1 and factor2 they both are empty and I want to populate them with the data of another table
...
0
votes
0answers
28 views
Size and performance different on UPDATE with/out intermediate CTE
I've got two tables with an indexes id column in common. I'm trying to add a new column in the target table using the value of a column in the source table. The two tables are rather large with couple ...
0
votes
0answers
21 views
Creating a trigger which stores updated value with old value in separate table
I'm trying to create a trigger which adds an old value to a table with the new value next to it.
For example, if someone were to update their address, I'd like the trigger to add the old address to ...
0
votes
1answer
27 views
Need help with correlated subquery
I have two PostgreSQL tables that each contain a column called date_of_incident.
They each also have a column called incident_id and this is how the tables are related.
I want to update all rows of ...
0
votes
1answer
45 views
How to loop through all table rows and perform subquery using values of same row?
I have a table structured as follows:
| reference | valA | valB | xml |
1 NULL NULL <xml stuff>
3 NULL NULL <xml stuff>
4 NULL NULL ...
1
vote
2answers
41 views
How to use TO_NUMBER function to update columns of a table Using oracle pl/sql
Hope you're doing well.
I have a table with this structure :
Table1(Cust_No Varchar2(50),
First_Name Varchar2(50),
Last_Name Varchar2(50),
Error_Code number(1),
...
1
vote
5answers
114 views
Updating multiple rows in a table with single query
I have been given the task to update a few rows in adatabase. There are two columns in which one is ID and the other is CURRENCY. I have updated these rows using the update statements that follow:
...
0
votes
1answer
21 views
Add a suffix to duplicates on PostgreSQL
Not sure about the term "undouble", I am just trying to add a unique index on a column but it contains doubloons, so I need to update data first.
Figure this data:
BEFORE update
1;foo
2;foo
3;foo
4;...
1
vote
1answer
20 views
MySQL uses range and where on UPDATE with the primary key
I have these simple SELECT and UPDATE requests on MySQL using the primary key, there are about 13 million rows in the table.
When running an EXPLAIN on both, I notice that the UPDATE one does not use ...
2
votes
1answer
31 views
MySQL: INSERT INTO SELECT issues
Consider the following tables:
=> table1
| aid |
| --- |
| a1 |
| a1 |
| a3 |
| a2 |
| ... |
=> table2
| aid | count | va | vb |
| --- | ----- | ----- | ----- |
| a1 | c1 | va1 ...
0
votes
1answer
46 views
Create an incremental number with year and month without updating the entire table using a trigger
My situation and code
I'll add a number to my MS SQL Server table. It must be an unique number beginning with the year and month of the time when it's created, followed by an incremental index number....
1
vote
1answer
41 views
Can a MySQL trigger include a where clause
I need to create a trigger that, any time a goat's sireRegNo column is updated, its sireName column is automatically updated to match.
I have a database that stores animal genealogy. Each row stores ...
0
votes
1answer
27 views
Free space of toasted content
I have just deleted a big part of the data stored in one jsonb inside my users table (Its not needed anymore) and just keep an id inside that column. I expected to free a lot of disk space with this ...
1
vote
1answer
38 views
How to increase the value of a column in a table by a constant by consulting another table?
I have prepared the database of the college given as
create table depts(
deptcode char(3) primary key,
deptname char(70) not null);
create table students(
rollno number(2) primary key,
name char(50),...
0
votes
3answers
28 views
How to prevent trigger from overwriting values that were explicitly set?
I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):
create temp table ...
0
votes
0answers
9 views
Best way to update user credits for API?
I am constructing an API that will use credits to determine if an API call can be made and the credits will be updated after a successful API request.
The simple procedure after an API call is made ...
0
votes
0answers
54 views
trigger to update if not exists in table oracle pl/sql
I want to create a trigger PL/SQL for the table: ap_supplier_sites_all (oracle EBS R12), in this table i have below information: vendor_site_id (primary key), vendor_id, attribute4 (varchar250), my ...
1
vote
2answers
40 views
SQL Server Update Statement joining on index column
Using SQL Server 2012, I have a stored procedure that updates data by doing a delete and insert similar to this:
DELETE FROM MyTable
WHERE EXISTS ( SELECT 1
FROM ...
0
votes
2answers
30 views
Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)
I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ...
1
vote
1answer
67 views
What is the fastest way to (session) lock table?
I have some triggers to log changes on a table to Log table.
On insert and delete I add row to the Log table and for update I add two rows.
The log table contain identity column, and I want the 2 ...
0
votes
1answer
30 views
Mark duplicate records on table
i've wrote the below query to update all rows as duplicate apart from the first one. I am getting an error with the aliases. Can somebody help?
UPDATE
`table`
SET
`table`.`statuscode` = '...
0
votes
1answer
33 views
Getting Values of the two rows before the selected row POSTRESQL
I want to update an row according to the values of two rows before the row how can i do that?
Let's assume my table looks like this
ID amount
1 1
2 3
7 5
And ...
1
vote
2answers
29 views
Replacing IN condition in Oracle
Oracle here. I have the following tables:
[orders]
===
order_id : integer constraint pk_orders primary key using index
order_name : varchar2(40 char)
order_ordered_by : integer constraint fk_shoppers ...
1
vote
1answer
73 views
Inserting few millions of rows: Does indexing happens along with the insert operation
I am inserting 50 million records to an existing table with a primary key , and a non clustered key.
My question is whether indexing of those will existing indexes will happen along with the insert ...
0
votes
1answer
31 views
How to add days to a date in PostgreSQL?
I have two tables and I want to execute an update similar to this:
UPDATE table1 set table1.date = table2.date + table2.numberOfDays
FROM tabel2
WHERE table1.id = table2.table1_id
Edit: table1.date ...
-1
votes
1answer
33 views
How to update a numeric type column to avoid many decimal points
lets assume I have a table with this entries:
thing: position:
ball 1001.25
pensil 1001.0625
car 1002.5
watch ...
0
votes
1answer
21 views
Update all values in a column with individual values
I ran into a problem where Excel and SQL Server have different "zero day" date conventions, so that all the dates currently in one of my tables are two days ahead of where they should be. Is there an ...
2
votes
0answers
20 views
Which table was updated by the last query?
When I perform an update query, MySQL server gives me information about how many rows were updated. But I wish to know in which table they were updated.
Is that information somewhere available?
The ...
0
votes
1answer
39 views
Write update in more efficient way with joins
How can I write this update with joins in 'more sql' way? I have been told that this is weird coding:
-- Updates recipient's account
UPDATE table1
SET balance = balance + @amount
WHERE ...
0
votes
1answer
37 views
How is a table locked regarding UPDATE-SELECT queries in PostgreSQL?
Is it possible to combine these UPDATE queries?
UPDATE myTable1 SET fld1 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 45;
UPDATE myTable1 SET fld2 = myTable2....
1
vote
1answer
24 views
How to update two records with two different values with two 'where' conditions at once?
In T-SQL, how to have this:
DECLARE @amount decimal(18,2) = 1000
UPDATE account
SET balance = balance + @amount WHERE accountID= 1;
UPDATE account
SET balance = balance - @amount WHERE ...
0
votes
1answer
16 views
How to update a column whose value is being calculated from the values exists in other columns of same table in MySql?
I've a table User_Engagement having columns :-
I was trying to implement this below formula on my update Query to calculate TotalInteraction of all columns in a single query:-
totalInteraction = ...
7
votes
1answer
119 views
Update Intersect hangs
We have this really weird behavior we just started experiencing with our update intersect statements. These were working fine, but now we are ingesting a pretty wide data source in terms of columns ...
13
votes
1answer
1k views
Cannot insert duplicate key row on a non-unique index?
We have encountered this strange error three times over the past few days, after being error free for 8 weeks, and I'm stumped.
This is the error message:
Executing the query "EXEC dbo....
1
vote
1answer
157 views
UPDATE table using CTE
I wanted to update the ranks of my table Performance and I found this solution, but I do not understand why this SQL code works:
WITH cte AS (
SELECT AllTimeRank, r = RANK () OVER (PARTITION BY ...
2
votes
0answers
31 views
Need to update a table with an iterative number based on a subquery
Massive edit to follow procedure and enable reproducing this in a fiddler somewhere.
Table 1, schedule:
CREATE TABLE `schedule` (
`scheduleid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`...
0
votes
1answer
27 views
Postgresql update a partitioned table, where update affects multiple partitions
If I have a partitioned table partitioned on PKey column, can I update (or merge etc) the table like below (or more complex jonining to another table) which will cause multiple partitions to be ...
-1
votes
1answer
30 views
SELECT Every Parent Table Record and INSET Multiple Record in Child Table Against the Parent_Id
Suppose I have two tables name company and screensavers as given below:
**Company:**
id (pk auto inc)
name (varchar)
**ScereenSavers:**
id (pk auto inc)
name (varchar)
path (varchar)
company_id (int)...
0
votes
1answer
36 views
PostgreSQL getting the latest value
I have started working on a postgreSQL database. There is a table keeping the record of stocks and every time a record is updated, a copy of the original row needs to be kept, with a flag added to say ...
