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
1
vote
2answers
491 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 ...
5
votes
2answers
5k 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
280 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 ...
0
votes
1answer
63 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
40 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
326 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
83 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
936 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
43 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
18 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
351 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
23 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
66 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) ...
2
votes
1answer
2k 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, ...
4
votes
1answer
536 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
49 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
25 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
1k 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
24 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
330 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
24 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 ...
4
votes
2answers
6k 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
48 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
443 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
32 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
229 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
48 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
188 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
32 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
38 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
32 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
613 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
221 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), ...
4
votes
5answers
7k views

Updating multiple values at a time

I have been given the task to update a few rows in a database. 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: ...
1
vote
1answer
762 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
330 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
125 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
69 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
972 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
182 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
182 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
326 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 ...
1
vote
2answers
141 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
150 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
78 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 ...
1
vote
1answer
264 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
238 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
757 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 ...

1 2 3
4
5
19