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.
811
questions
0
votes
0answers
14 views
Why Mysql only updating single row when the where condition have multiple rows? [closed]
My this query always update only single row, while there are multiple rows with gender 'M':
UPDATE users
SET
hire_date = DATE((SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2014-1-1') + FLOOR(RAND() * (...
0
votes
1answer
14 views
Update large number of row in MySQL table
I'm using relational database(MySQL 5.7). On this database i have a table called customer_transaction. On this table i have 4 columns: id, customer_id, type, amount
|id|customer_id |type |amount|
...
1
vote
1answer
14 views
Create a key-preserved view across Oracle tables with descending indexes
I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and:
Do not have primary keys - only a ...
0
votes
3answers
35 views
update one column based on query containing another (joined) table
Consider the following select query:
SELECT s.id, s.event, s.column, e.oldcolumn
FROM section as s
INNER JOIN event as e
ON s.event = e.id
WHERE s.id = 1
Now I wish to update the section table so ...
0
votes
1answer
20 views
How to add all combinations of two pairs of key-value to a table?
I have a database of items, as each item has a set of key-value pairs such
place => london
field => engineering
since I frequently need to find items having the same two key-value sets. I ...
0
votes
0answers
11 views
How to know user intentions when updating a record in a table [closed]
I am using Entity Framework to manage an SQLite db.
The user can create an entity object (with the required primary key for identification) and pass it to me so I can update it in the db. My problem ...
0
votes
1answer
26 views
UPDATE SET values ignoring set ordering
I'm trying to update 2 rows at once, I want to:
1) Set coupon.start (datetime) to the original account.vipExpires (datetime)
2) Add coupon.days (int) to account.vipExpires (datetime)
3) Set coupon....
-2
votes
1answer
37 views
Am not able to execute the update stmt using Multiple Joins..SQL DEVELOPER [closed]
UPDATE artikel A
SET A.art_haccp = (SELECT DISTINCT a.art_haccp
FROM artikel A,
warengruppe W,
sparte B,
...
0
votes
1answer
14 views
Bulk update an address column where the address was duplicated with a delimiter separating the duplicate?
Sorry if my title doesn't make much sense, I wasn't sure how to word it though.
Basically I imported a file containing 500+ of stores and information about them. For some reason the [Address] column ...
0
votes
2answers
23 views
slow UPDATE of rows by COUNTing from another table
I try to update a table by counting rows from another table as
UPDATE t1 SET c1 = (
SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
)
strangely, this query stuck at Sending data state ...
0
votes
1answer
10 views
What is the safe way to update production data if data found inconsistent
I am in a small company and require to fix a bunch of data in production which are inconsistent. I have written the script to handle the fix.
I understand that writing sql to fix data is way more ...
1
vote
1answer
43 views
update matching column value in anywhere in database sqlserver
I have assetid column in multiple table in my Database and Asset(main) table
I want to update all occurences of tables in all occurences of inserts in database assetid column where value has 1020 to ...
0
votes
0answers
11 views
Psycopg2 using value SET before assign to other column
I have a table with columns as below: (id, col1, col2, col3, col4). I need to insert new row to this table, with given data is a dict: data = {'id': 1, 'col1': 1, 'col2':1, 'col3':1, 'col4': 1} for ...
1
vote
2answers
67 views
How to combine two columns into one?
I have a table in PostgreSQL 11 with two varchar columns like this:
A | B
---+---
m | n
x | y
Now I want to add a column C which should contain the contents of A and B (concat(A, B)):
A | B ...
0
votes
0answers
27 views
MySql uncomplated Upgrade Error
I faced showed bellow error every apt upgrade. An mysql server frequently down. I try backup my database with mysqlpump, this backup not contain tables values. Only table construct. How can I fix this ...
0
votes
0answers
18 views
Problems Add/Update Big Data on PostgresSQL
I want to add large amounts of data to the table.
Before adding, I check whether the data exists in the table or not.
I am dealing with the following:
Example:
Table users
id | name | address
.....
0
votes
0answers
26 views
UPDATE records in one table, several times the message (1 line affected) and then the actual message of the update
I have discovered the following oddity:
When I execute a SELECT statement, I get my desired 753 records displayed.
But when I convert this SQL to an UPDATE and I execute an SQL with the following ...
1
vote
1answer
38 views
update using sum
I am trying to update the totalNumber column of the AccountDetails table to be the SUM() of the totalNumber column of the Trans table, where the accountID and the assetID columns match.
The is the ...
2
votes
2answers
130 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
19 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
32 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
22 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 ...
5
votes
2answers
427 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
64 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
95 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
279 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
67 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
83 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 ...
2
votes
2answers
202 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
75 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
60 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
50 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
54 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
79 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
210 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
40 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
39 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
46 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
57 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
256 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
78 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
36 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
92 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'...
