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.
794
questions
2
votes
2answers
116 views
Can I use OUTPUT from an UPDATE in an INSERT?
I want to add a record to my app's "SystemSettings" table and set the PK value using the value from an UPDATE. The PK value comes from the "TS_LASTIDS" table which contains the maximum PK value for ...
0
votes
1answer
14 views
how can I update some table rows using data from existing rows?
I have created a table where it stores some multilingual store data with this structure
create table store_locator_store_lang
(
id_store int(11) unsigned not null,
lang_id int not null,
...
0
votes
1answer
27 views
SQL SERVER: Header Blocker Across Multiple Databases with wait type CXCONSUMER
We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker ...
0
votes
0answers
21 views
Insert select - using selected value in ON DUPLICATE KEY UPDATE
In the following query, oversimplified from my actual scenario but close enough:
INSERT INTO foo(bar_id, baz)
SELECT
id,
5 AS new_baz
FROM
bar
ON DUPLICATE KEY UPDATE baz=new_baz
I get ...
4
votes
2answers
408 views
Why is there an aggregate in this UPDATE plan?
Given this
declare @Data table (id int, fact char(1));
declare @Summary table (id int, collected varchar(99));
insert @Data(id, fact)
values
(1, 'a'),
(1, 'b'),
(2, 'c'),
(2, 'd'),
...
0
votes
2answers
57 views
Slow update performance
There is Postgres 10 (10.5 (Ubuntu 10.5-1.pgdg16.04+1)).
The server has 256 GB RAM.
shared_buffers = 64GB
effective_cache_size = 192GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
...
0
votes
1answer
42 views
Will subquery (using IN) run multiple times for update statement (SQL Server 2017)
Where an update statement is updating the value returned by a subquery - will the subquery be run for each row?
UPDATE a
SET DM = 1
FROM TableA a
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA ...
0
votes
1answer
56 views
INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query
I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of id ...
2
votes
1answer
58 views
Postgres UPDATE with data from another table - index only scan used for correlated subquery but not join
Context
I'm tuning a bulk UPDATE which selects from another (large) table. My intention is to provide a covering index to support an index only scan of the source table. I realise the source table ...
-1
votes
1answer
21 views
Update table column only if the ids don't exist in another table
I have two tables that are in play here. A users table and a users_roles table. I need to update the users status column to 0 for almost all users except for a few, we can target these users by uid ...
1
vote
2answers
48 views
PostgreSQL remove numbers before first comma
I am new to SQL however, I am looking to update a table where column called "scrap_value" and rows contains a set of numbers in an array e.g.
{100000,125000,150000,175000,200000}
I want to remove the ...
1
vote
2answers
74 views
Why am I getting a deadlock for a single UPDATE query?
I have two processes which execute code like this in parallel:
begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;
This results in deadlocks.
My understanding of what ...
1
vote
1answer
72 views
How can I lock rows FOR UPDATE without returning data over the connection?
I want to do something like this:
begin;
select * from foos where owner_id=123 and unread=true order by id for update;
update foos set unread=false where owner_id=123 and unread=true;
commit;
The ...
-1
votes
1answer
49 views
Updating tables in sql
I've made this procedure to update the fields on a table:
CREATE OR REPLACE PROCEDURE actualizarEncargadosP(
OID_EN2 IN encargadosp.oid_en%TYPE,
nombre2 IN encargadosp.nombre%TYPE,
apellido2 IN ...
1
vote
2answers
39 views
Postgres: update single boolean column on 12 million rows
I have a table with 12 million rows. There are 16 columns on the table, one of which is a boolean.
After a certain business process, a large chunk of these rows will have this boolean set to false (...
0
votes
0answers
34 views
Use SELECT query to retrieve UPDATE information
I am confused about inability of using UPDATE query inside SELECT queries. I got this idea, it would be amazing to use queries like follows for elegant reporting purposes
My non-working code:
SELECT ...
0
votes
1answer
46 views
MySQL multi-table update
I'm a bit confused about the proper way to update one table, based on the values of corresponding rows in another table.
Here is the query I have:
UPDATE
features,
images
SET
features....
0
votes
1answer
34 views
Update table with values from another table runs really slow
I have a PostgreSQL table (tab_A) with about 32 million records, and I have a second PostgreSQL table (tab_B) with about 4000 records. tab_B contains 3 fields whose values I intend to pass on to tab_A ...
0
votes
1answer
34 views
in SQL databases does an update rewrite all columns in the row or only those in the SET tuple?
i was wondering how a sql update works in two scenarios:
table with 3 columns: a, b, c
say the data value for a has to be changed.
1: update on a row setting all columns a, b, c (where b and c have ...
-2
votes
1answer
77 views
Three updates in order in the same script won't work as intended, why?
I have a table with a field called gender. It's an int. 1 = male, 2 = female.
Here's what was done, in the same script
UPDATE table
SET GENDER = 9
WHERE GENDER = 1
AND (other criteria);
UPDATE ...
3
votes
2answers
153 views
SQL Deadlocks - Same Stored Procedure Called Concurrently
I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to ...
2
votes
0answers
38 views
Why is it faster to SELECT my logical CASE field into a new temp table as opposed to UPDATing the original temp table to SET the field?
I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it #TempTable1.
#TempTable1 has a BIT field called ToInclude which is set in a ...
0
votes
1answer
17 views
Does range improve match operation and which runs first, range or match
I am using MySQL version 5.6.10
I have a very very large table large_table and a relatively small table small_table
The small_table has only the records of the date 2019-11-20
There is a column I ...
0
votes
1answer
38 views
What makes for a good index in this scenario?
I have a pretty big temp table (~5 million rows) that I need to update a few fields on.
Here's an example update statement:
UPDATE T
SET CarModel =
CASE
WHEN CarModel = '350z' AND ...
-1
votes
1answer
39 views
Update flag based on max last updated date within in the group
Say for example I have three records:
Name Email Group ID Last update date Master Record
dinesh.s dinesh.s@xyz.com 10 1 Apr 2019
dinesh.s dinesh.s@...
0
votes
1answer
21 views
Update a record whilst creating a link to a parent record
Someones account was deleted from a system we have. They created a new account for her and moved on. But it turns out she wasnt deleted from SQL - when they delete in the app, the record is marked as '...
0
votes
2answers
54 views
Read uncommited does not work in UPDATE WHERE NOT EXISTS
I'm not able to force SQL Server (2016) to respect values updated in the same statement.
What I need to do is to update old values (column a in table #t in demo code below) with mapping table (#u) ...
1
vote
1answer
95 views
Update dynamic column names using keys from jsonb argument
I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, ...
3
votes
1answer
29 views
Select and Update MAC Addresses to Same Format
I have a mysql database with a macaddress column, this contains thousands of mac addresses in various formats; uppercase, lowercase and with colons. See below for an example;
+-------------------+
| ...
0
votes
1answer
34 views
How to count multiple occurences on the same date
my question is related to the table down below, where I have to count multiple occurences of "Hours" if they happen on the same "Date" by the same person.
CREATE TABLE [dbo].[Time](
[GUIDTime] [...
0
votes
0answers
22 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
34 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
21 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
49 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
11 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
445 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
31 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
24 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
144 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
42 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
38 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
31 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
28 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
28 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
93 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 ...
0
votes
2answers
66 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
704 views
Updating multiple values at a time
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
59 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
34 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 ...
