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.

Filter by
Sorted by
Tagged with
0
votes
0answers
11 views

Why Mysql only updating single row when the where condition have multiple rows?

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
276 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
82 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
201 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
53 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
254 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
91 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'...

1
2 3 4 5
17