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
1answer
18 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
42 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
35 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
26 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
25 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
25 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
21 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
38 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
21 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
64 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
20 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
30 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
71 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
30 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
32 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
20 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
19 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
38 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
36 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
23 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
15 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
114 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
509 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
87 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
30 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
26 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
27 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
35 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 ...
3
votes
1answer
72 views

An uncommitted transaction updates a column; how can a different transaction see the old column value?

Let's assume the following case. There is a students table: id | name ----------------- 1 | Mateus A "User A" executes the following statement without committing the transaction: update students ...
7
votes
2answers
97 views

XQuery doesn't update XML data

I am trying to run the script to update an XML column: UPDATE DataImpTable SET serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1] with "9876"') ...
0
votes
0answers
22 views

UPDATE query in small tables taking forever

I'm having trouble creating a UPDATE query to perform the following: I have two tables, ed is a 11k rows table with areas from multiple constructions and a key lote_key wich links to the table unid, ...
4
votes
1answer
265 views

Seeing 2 very different execution plans for an UPDATE between test & prod environments

I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the ...
0
votes
1answer
42 views

How to update an element in json array with where clause?

I came across this question on how to update nth element of array, but it has nested json values in it instead of a plain array of strings. Here are some sample json values from jsonb column of the ...
1
vote
0answers
870 views

Postgres bulk upsert performance

I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
1
vote
1answer
44 views

how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger?

SOLVED Edit: Trigger function and trigger are working fine with no errors, problem was with my database itself, dropped the database, created new, it's working now. How do I prevent UPDATE on any ...
2
votes
2answers
222 views

Deadlock on two update statements on the same page

I'm receiving multiple deadlocks in one SQL Server. The deadlocks occur when the same query with different parameter values is being issued on the same database: Example: Query 1: UPDATE Tabel1 ...
1
vote
2answers
29 views

update older records in each group

There is a set of data, like | id | serial | version | is_deleted | -------------------------------------------------- | 10 | AAAAAA | 2019-04-14 15:28:08 | 0 | | 22 | AAAAAA | ...
1
vote
3answers
90 views

Mysql innodb table dead lock while batch update in multiple thread

I've two tables of innoDB type and I'm updating one table using batch statement from 10 thread (one batch statement per thread) but some time I'm getting dead lock. java.sql.BatchUpdateException: ...
2
votes
0answers
107 views

Stored procedure to UPDATE row to “closest” match in one table from data in another table

I have a table called ThawLaneA that has a populated column ThawTime that has DATETIME value. The first row starts at a specific time, and each row from there "increases" by a specific amount of time (...
1
vote
1answer
467 views

MySQL - Creating sort index and update query takes forever

I have the following MySQL query UPDATE PCL AS p JOIN HST AS h ON h.SrNo = ( SELECT hi.SrNo FROM HST AS hi WHERE hi.SYM = p.INST AND hi.DATE >= p.Date AND hi.HP >=...
1
vote
1answer
101 views

Updating values in table based on ranges defined in another table

I have two tables definitions and history with the following schema: create table definitions ( id UNIQUEIDENTIFIER, revision INT, majorVersion INT ); create table history ( id ...
0
votes
2answers
49 views

Is there a way to speed up this update query using joins or any other method

I have an update query that selects data from a table (HST) and updates it into another table (PCLN). The PCLN table contains ~17000 rows while the HST table contains about 1.1 million rows. I have ...
0
votes
0answers
316 views

Unknown column “X” in field list on delete, but the column exists

I'm using MySQL and i'm trying to run a delete statement as follows: DELETE FROM `myTable` WHERE `nincidusua` = 84 and the following error occurs: Unknown column 'nincidusua' in 'field list' My ...
1
vote
1answer
15 views

Remove text from columns that may or may not be present multiple times

I have the following table in SQL Server 2008. create table OrgUnits ( OrgUnitId int not null constraint PK_OrgUnits1 primary key, OrgUnitName varchar(100), OrgUnitLevel int not null, ...
0
votes
1answer
329 views

change mysql datatype column from string to json

I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON. I ...
2
votes
1answer
131 views

How can I identify non deterministic updates occurring in my SQL Server?

SQL Server books online states Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement ...