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.
869
questions
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 ...
David Browne - Microsoft
29.2k2 gold badges31 silver badges66 bronze badges
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)...
Erwin Brandstetter
126k16 gold badges289 silver badges429 bronze badges
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) ...
David Browne - Microsoft
29.2k2 gold badges31 silver badges66 bronze badges
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
...
