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
579 views

Trigger on 2 tables : issue with INSERT and UPDATE

This is related to my previous question: Trigger to cascade updates to a second table. In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields ...
-2
votes
0answers
27 views

conversion failed when converting datetime from character string while inserting and updating to database

I'm trying to insert time and date separately and calculate if it is late to the set time and then insert it to database... here's my try code for update string Date = DateTime.Now.ToString("dd-...
1
vote
1answer
34 views

postgres query performance

I can't find a good way to rewrite this query or improve the performance: query: update company_holding c set issuer_stim_buy_quarter = sq.estim_quarter_buy from (select h.company_id ,h....
1
vote
1answer
21 views

How can I make UPDATE to return an error if no rows are affected in Postgresql?

In my application code, I have an ordinary UPDATE statement to update some row's properties. However, I've encountered a bug where this statement was provided with an incorrect ID and no row was ...
2
votes
1answer
3k views

How to update primary key table based on foreign key with ON DUPLICATE KEY UPDATE

I have a users table with autoincrement id as primary key, and unique key on email. The email and name fields are from facebook. +----+-------+------+ | id | email | name | +----+-------+------+ And ...
2
votes
2answers
38 views

How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014?

For example, I have a table of: id | code | name | type | deviceType ---+------+------+------+----------- 1 | 23 | xyz | 0 | web 2 | 23 | xyz | 0 | mobile 3 | 24 | xyzc | 0 | web 4 ...
6
votes
1answer
135 views

Updating an indexed view with NOEXPAND

I will preface this by saying that this is a cross post from an unanswered question on Stack Overflow. I am not doing this simply to get more views on the question, I would like the DBA community to ...
3
votes
1answer
61 views

Syntax shorthand for updating only changed rows in UPSERT

I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed)...
2
votes
3answers
249 views

Query to update one column of a table based on a column of a different table

I have two tables with below structure : Create table Customer_info (Customer_num varchar(50), Customer_Branch int); Create table Customer_Transaction_Info (Customer_num varchar(50), ...
0
votes
0answers
30 views

Is it possible than two simple updates on same table interlocks (deadlock)?

Table A is: id integer version varchar data jsonb (large data 1mb) fkToBid integer (references B.id constraint) Table B is: id integer other... Processes are aggressively running the two updates ...
1
vote
1answer
41 views

Postgres 9.5 deadlock with many updates on a table + many “select for update” on another table

I have two tables: "machines" and "magictools". "magictools" refers to "machines" with a foreign key. I face a deadlock problem while executing many of these ...
15
votes
2answers
43k views

On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` VARCHAR(...
0
votes
3answers
112 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 ...
5
votes
2answers
3k views

Update rank on a large table

I have a mysql table that has now 12 million records and I'm trying to update a column (rank) with the following UPDATE code: SET @r=0; UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY ...
0
votes
2answers
79 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
2answers
30 views

MySQL Insert Values Into Normalized Relational Database (multiple tables) at the same time

I have normalised my database to 4NF. How do I now insert values at the same time when records have been split into multiple tables? most of the child tables reference their parent table CUSTOMER ...
1
vote
1answer
31 views

Receiving error “invalid identifier” while trying to update a table in Oracle

I'm trying to transform a Merge query into an Update query for getting better performance but I could not succeed. Tables structure: create table TEST_FILE_MERGE ( customer_num NUMBER, ...
0
votes
2answers
34 views

Make two different updates with two different where conditions

Although this question is very similar, I don't think that it is also the best approach for my case, though it seemed to work. Consider the following example: CREATE TABLE messages ( writer VARCHAR(...
0
votes
2answers
90 views

How to accomplish such insert-or-update strategy?

Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better. I've stumbled upon a business requirement that's kind of hard for me and I was ...
2
votes
4answers
537 views

RowNum For T-SQL (Not TOP #)

Trying to run an update, but I want to step through it as it's quite a bit of data and don't want to blast it all at once. In oracle it's a bit easier to select the rows I want because you can ...
2
votes
1answer
80 views

Overwrite event - data from SQL Server to MySQL

In MySQL is it possible to execute an update query with values selected from external SQL Server database? I'd like to execute every night a timed query, which overwrites some data in MySQL with data ...
1
vote
2answers
65 views

How can I efficiently delete records when a counter reaches zero in PostgreSQL?

Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this ...
1
vote
2answers
371 views

Replacing MYSQL table with data from another table - no downtime

I have two tables with identical structures: Table1 and Table2 id INT PRIMARY KEY, a TINYINT, b TINYINT, c TINYINT Table1 is actively being used by queries from other sessions; Table2 is not ...
0
votes
1answer
36 views

Unexpected unique constraint violation during update

In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records ...
0
votes
1answer
304 views

Correcting and Detecting overlapped dates with CTE

I have a CTE that marks (as bits) overlapped records based on certain criteria. After filtering, I need to update these records to "correct" them and make them sequential, but I'm getting a 4104 on ...
1
vote
1answer
29 views

Mysql JSON update by value of a key

I have a table like: CREATE TABLE `campus_tb` ( `campus_id` int(11) NOT NULL AUTO_INCREMENT, `campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`...
1
vote
1answer
47 views

MS SQL Server 2019 - deadlock after row update by primary key, possibly caused by trigger

I've a table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[users]( [id] [int] IDENTITY(1,1) NOT NULL, [username] [nvarchar](65) NOT NULL, [nickname] [nvarchar](65) ...
0
votes
1answer
2k views

SQL Server updates missing from WSUS

I don't use WSUS for SQL Server patching because we have so many different compatibility issues, requirements and downtime OLA's that it makes it impossible to deploy anything in this way - not sure ...
2
votes
1answer
78 views

Index that mimics full-text-index

I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a .sdf file and Sql Server Compact ...
1
vote
1answer
72 views

Inserting Data using Cursor but check if the data already in the table

I want to check if data already exist in a table before I do an insert using a cursor. So the cursor will pick a record, check that record doesn't exist in the table. IF it Doesn't EXIST then Insert, ...
4
votes
2answers
2k views

How can I override the MySQL DELETE command to update a field rather than to remove the row?

I wonder whether it's possible to override the functionality of MySQL's DELETE command to update a field on the selected row rather than to delete it completely. This would make it possible to set a ...
0
votes
0answers
30 views

MySQL - how to optimize select within a range bound by values in 2 different columns - innodb

How do I optimize select within a range bound by values in 2 different columns with MySQL innodb? I'm matching words to the pages on which they were printed. Each instance of a word points to only 1 ...
2
votes
1answer
87 views

UPDATE on column referenced in sub-query

We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following: update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, ...
5
votes
2answers
8k views

How to limit rows in PostgreSQL update statement

My table employees contain more than ten million data. i want to update 10k rows in commission_pct column where commission_pct is null and set zero(0) value. in oracle I can easily solve this by ...
0
votes
2answers
49 views

Merge two separate UPDATEs into one

I have two separate UPDATEs as follows: UPDATE mytab SET colA = 1 WHERE id IN ( SELECT id FROM mytab JOIN othertab ON mytab.otid = othertab.id WHERE othertab.Lock = '...
0
votes
1answer
157 views

I Want To insert data in transaction table and at the same time update customer table in codeigniter

(codeigniter) I Want To insert data in transaction table and at the same time update customer table . I want to Add value of ChangeDue in transation table To value now_debt in Customer Table ...
1
vote
3answers
201 views

Postgres- update a value in a array on particular index

I have a column named ids that is of type bigint[]. ids cane be null or of any length It has values based on certain hierarchy. My use case is I want to update value that is at a particular index. ...
1
vote
1answer
85 views

Translate SQL Server update statement to MySQL

I have a development environment with MySQL and a production environment with SQL Server. This update statement works with SQL Server: UPDATE THIS SET <list_of_column_assignments> FROM <...
0
votes
2answers
43 views

Updating a column name to be a concatenation based on the same table

Need Help in SQL SERVER! I have two tables: Students: Student_Id, Student_Name Grades: Student_Id, Profession, Grade. I need to change the column Profession to have the value Profession + Grade only ...
4
votes
3answers
62k views

mysql update query error Code 1054 unknown column in field list

Error: Code 1054. Unknown column 'U2.id_naslov' in 'field list' gets thrown on this simple query in MySQL Workbench: UPDATE krneki_1 AS U1, krneki_2 AS U2 SET U1.id_naslov = U2.id_naslov WHERE (U2....
0
votes
1answer
33 views

Nested push in Mongoose

I have a user schema like that: name: String, nick: String, email: String, password: String, bio: String, color: String, followers: [String], followings: [String], posts: [{ author: { ...
0
votes
0answers
29 views

Performance issue with update in postgres

I've got a pretty simple update statement in my application in one of the APIs. UPDATE "table" SET "count" = "count" + 1 WHERE "id" = $1 The problem is when ...
0
votes
2answers
103 views

Update a table with data from another table

I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. ...
0
votes
0answers
16 views

Can you use a SELECT … FOR UPDATE cursor in a stored procedure to enforce row-level locking?

From https://www.oreilly.com/library/view/mysql-stored-procedure/0596100892/re37.html: CREATE PROCEDURE needy_bonus( ) BEGIN DECLARE v_employee_id INT; DECLARE v_salary ...
0
votes
1answer
293 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
1answer
40 views

PostgreSQL update column in one table with value from another, based on key from first table

Sorry about the title, I wasn't sure how best to word this. My situation is this: I have two tables: paper_author [paper_id, author_id, author_name] authors [author_id, author_name] Right now, the ...
0
votes
0answers
9 views

How to apply many UPDATE statements in one network call

Can we apply many UPDATE statements in a single network call? Figure many UPDATE requests on a JSONB field of one record. I would like to update many places in the JSONB field. All updates apply on ...
0
votes
1answer
2k 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 ...
0
votes
2answers
48 views

Why does my table keep getting corrupted after removing large amounts of data?

I have a database that is about 400 gigs. The majority of the space is consumed by images of utility bills stored in string form as varchar(max) in a single table. It's a poor database design that I ...
0
votes
2answers
105 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 ...

1
2 3 4 5
18