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.
0
votes
0answers
24 views
Dynamically Updating Multiple Columns - SQL Server Management Studio [on hold]
I would like to pass a statement like the below into SQL Server Management Studio (through Pyodbc in Python):
UPDATE tab SET (column, ...) = (val, ...)
which I found from this forum. However, this ...
2
votes
2answers
55 views
How to optimize a table for a very high-frequency updates?
I have a table that contains a list of tasks that need to be run periodically:
applaudience=> \d+ maintenance_task
Table "...
0
votes
1answer
40 views
Checking update value before update
First time posting so apologies if this has been asked already.
I have a table in SQL Server that is being updated from many different locations with data that I don't want in the table. I have come ...
0
votes
1answer
19 views
How to update a sequence column using single update statement
I have a table, lets call it foo which consist a column priority and has a unique constraint on it.
lets assume below is the current state of foo table :-
mysql> select * from foo;
+-----+--------...
-3
votes
2answers
34 views
I need to copy values of a column from 1 DB to another DB on a diff column but has the same primary key
i need to copy the value of column of nickname and sysnbr to another DB on a diff table/column:
I need to copy the nickname column and sysnbr column to this table. this is on another DB
nickname = ...
0
votes
0answers
23 views
Calculating Column X Value Dynamically, and then Storing the result
First off, thank you for the time anyone takes to help me. I should start by saying I am entirely new to SQL. A friend introduced me to the other day and I am where I am from literally no knowledge of ...
1
vote
1answer
37 views
Update “oldest” row
I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n).
I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
0
votes
0answers
28 views
Updates with many rows and lock escalation
I've got a table containing around 15 million rows, where each day a number of them will need to be updated. The first time we run this, it will probably update ~13 million rows, and then after that ...
-1
votes
1answer
26 views
Improving performance of a query update that uses md5
I need to optimize this query, or change some configuration, because execution takes too much time.
Query
with rows as (
update hotmail.clean_us_usdataclean_1 set offers_excluded=case
when
...
0
votes
1answer
17 views
Update record column with id found in the table of an associated model
Given three tables:
A users table with id's:
╔════╗
║ id ║
╠════╣
║ 1 ║
╟────╢
║ 2 ║
╟────╢
║ 3 ║
╚════╝
A purposes table with user_id's
╔════╤══════════╗
║ id │ user_id ║
╠════╪══════════╣
║ ...
4
votes
1answer
78 views
An UPDATE runs unexpectedly on MySQL 5.7 whereas it runs as expected on prior versions
I use a column called sequence on tables where I have to re-sort blocks of rows into an arbitrarily order.
CREATE TABLE `tkmemberstage` (
`ID` bigint(22) unsigned NOT NULL AUTO_INCREMENT,
`...
1
vote
1answer
87 views
Should SQL Server 2000 throw an error if update statement fails within a SP?
I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was ...
-2
votes
2answers
52 views
How to UPDATE along with INSERT INTO SELECT [closed]
I added some entries from table2 to table1 as
INSERT INTO table1 (title) SELECT title from table2 WHERE ...
how can I UPDATE table2 for the SELECTed or INSERTed entries as
UPDATE table2 SET status='...
0
votes
1answer
24 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 ...
0
votes
1answer
31 views
Subtract multiple quantities in table2 from table1
How can I subtract the quantities in table2 from table1?
I'm using SQL Server 2014.
Table1
P_ID name quantity
1 carrot 100
2 eggplant 100
3 Avocado 100
...
0
votes
2answers
96 views
Understanding why a query is slow
I have a new column in SQL Server 2016, on a table of type bit. (I wanted it to be a persisted calculated column but was overruled and asked to make it a bit that is maintained by program code). Its ...
1
vote
2answers
61 views
Concurrent UPDATEs on INNODB tables in MySQL
I need to UPDATE all rows for multiple columns on a large INNODB table. Each UPDATE processes one column separately and takes about 1 hour. I have to do this for 10 columns so it would take 10 h I do ...
0
votes
0answers
23 views
find out postgresql query progress [duplicate]
I have ran an UPDATE query in postgresql. After 5 days it has not finished yet. Is it possible to check percentage of completeness of my query which I have ran previously. I want to know if it takes ...
1
vote
1answer
63 views
Insert Trigger to a Linked Server error
I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the ...
1
vote
1answer
213 views
MongoDB update multiple sub documents inside array, each according to a condition
I have a document like this:
{
wid: 1,
items: [
{nid: 1, sent: 900},
{nid: 2, sent: 12},
{nid: 3, sent: 0},
{nid: 4, sent: 1}
]
}
Now consider I want to ...
0
votes
1answer
30 views
How to UPDATE from JOIN with GROUP BY
In a SELECT query of
SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0
How can I turn this query to UPDATE as
UPDATE b.number ...
0
votes
1answer
53 views
Is it harmful to update columns which aren't changed?
Say I have a table with some columns:
CREATE TABLE t
(
key int PRIMARY KEY,
c1 int,
c2 text,
c3 timestamptz
);
I now fetch a row from the table:
SELECT * FROM t WHERE id = 180;
...
1
vote
1answer
36 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
...
0
votes
2answers
80 views
UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT'
I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table.
To me, the query is ...
2
votes
1answer
117 views
Temporarily disable indexes before updating whole table
For testing purposes I periodically restore production backup into a new database (AWS RDS PostgreSQL instance) with pg_restore. In the new database I run SQL queries that anonymize sensitive data. ...
0
votes
1answer
28 views
Update only if entry does not exist
I have to update many records. I don't want to duplicate the entries. The table concatenates the value to the end. So in theory, I could add the value twice.
I want to put a check to make sure the ...
1
vote
1answer
39 views
Why is this very simple UPDATE so much slower than the SELECT it contains?
In MariaDB 10.3.9:
SELECT COUNT(*) FROM photos
vs
UPDATE counts SET countValue = (SELECT COUNT(*) FROM photos) WHERE countName = 'photos';
The select takes 1.1s vs the update which takes 2.6s. ...
1
vote
2answers
82 views
Update single field in a document (document.update($set)?)
Can't seem to figure out how to update a single field.
Whatever I try, to update one field (picture: "url") the rest of the fields are being removed and set to null.
I use the following command now:
...
0
votes
1answer
21 views
Scrambling PII data in CLOB columns
I am working on scrambling PII tags in CLOB columns such as <FirstName> or <LastName> or <SSN_Number>.
so I am using the below query to achieve the same.
UPDATE /*+PARALLEL(8)*/ ...
0
votes
1answer
25 views
What is the best solution to prevent picking the same value (by multiple requests)?
Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick ...
0
votes
2answers
53 views
creating a trigger after update exclude a column?
I want to create a trigger that set a column to to_sync true if the other columns has been changed.
CREATE TRIGGER [dbo].[product_trig]
ON dbo.product
AFTER UPDATE
SET NOCOUNT ON;
IF NOT ...
1
vote
1answer
38 views
What is the function that determines the maximum value in a series?
Say I have a table with columns C and D (among others but these are the relevant ones) with the date type.
I am instructed to update the values in C to be 90 days before its corresponding value in D, ...
3
votes
2answers
33 views
How can I instantly update a table in a database with the columns values from another database?
I have database_1 and database_2 both on the same server.
How can I instantly update the USER table in database_2 when data is inserted into the USER table in database_1?
Structure of both tables:
...
0
votes
1answer
378 views
Trigger to check if a column is updated and another one has a specific value
I'm writing a trigger for a SQL Server database. What I want is, when a table is updated, check if a specific column is updated or not and if another column has a value = 'W' or not. If it is so, the ...
1
vote
1answer
28 views
Why does this tsvector update use so much space?
This is my table structure:
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------------...
0
votes
2answers
28 views
Update values from one table to another where key values are not unique
I've been trying to figure this out for hours and can't seem to get it. If it can be done, any help would be greatly appreciated! I hope I'm clear with what I'm asking :-)
Consider the following:
...
3
votes
1answer
264 views
Order of Execution of Set clause in Update Query (Postgres)
I recently came across this strange behaviour in Postgres. I had a table like the following one:
sasdb=# \d emp_manager_rel
Table "db3004db.emp_manager_rel"
Column | Type | ...
0
votes
1answer
41 views
Update statement [closed]
I need to update values I get when I run this select statement
select *
from POSTPAID_RERATE_NEW_1 a
left join CUg_IND b on a.service_class = b.service_class
where CALLINGGROUPNO = CALLEDGROUPNO ...
1
vote
2answers
378 views
How to run update query on multiple table via joins simultaneously on postgres?
In MySQL, I can do a query like this
UPDATE
prd_sectionshapename se
inner join
prd_shape s
ON
s.id = se.shape_id
SET
se.company_shape_name = 'ABC',
s.name_en = 'Another name'
...
1
vote
2answers
156 views
Unable to update MySQL table from linked server on SQL Server 2014
I'm using SQL Server 2014, with a linked server to a MySQL database on AWS. I need to update rows on that linked server, using data that exists on another linked server. And if that wasn't complicated ...
1
vote
1answer
92 views
Best way to prevent deadlocks and server locking for single row Insert/Update transaction?
I have Update transaction that will run for single row at the time. However, my application is multi thread and there might be more than one user at the time trying to access same table. While doing ...
3
votes
0answers
61 views
Where is Newid(), Rand(), and Sysutcdatetime() new value per query vs. row behavior documented? [duplicate]
Within a single query, all current time functions like sysutcdatetime() return a single point in time that is re-used throughout the query for each instance (e.g. of sysutcdatetime()) no matter how ...
1
vote
2answers
78 views
Update selected data from same table column as currency
I am trying to update selected data from a table column and format this data as currency.
My table column holds strings that have both mixed whole numbers and already formatted data. This was due to ...
0
votes
1answer
33 views
Affected rows after update with OpenQuery
Is there a way to get the affected row count, when updating through an connection server?
I'm using MSSQL connected to MySQL with ODBC.
UPDATE OPENQUERY (MyConnection, 'UPDATE recordid, status FROM ...
-3
votes
1answer
56 views
Updating a string field by concatenating 2 int columns
I have 2 columns, an int and a tiny int, and I want to update another column, a varchar one, with the value column1 + '-' + column2. Something like this pseudo code:
UPDATE COLUMN3 varchar = CONCAT (...
0
votes
1answer
254 views
mysql update based on select result, How combine?
Here are two SQL statements. One is an select statement and the other is a update statement. I want to combine them.
Select
SELECT * FROM test WHERE `id`='1'
Update
UPDATE test SET `select_count`=...
1
vote
1answer
52 views
Innodb updates records with delay
I have two tables in my database like these:
CREATE TABLE `main` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ...
0
votes
0answers
29 views
Do any of the PostgreSQL releases for Windows support automatic updates?
PostgreSQL has two distributions for Windows,
EnterpriseDB
BigSQL
Do either of these Windows distributions support automatic updates for the term of support? Something that looks for the updates ...
1
vote
1answer
152 views
Which is faster to update thousands of table rows?
With stored procedures in PostgreSQL v10.1.2, which is the fastest way or which is better: to check if rows exists and then update or try to update directly with the possibility that no rows matching ...
0
votes
1answer
25 views
Update MySQL with Solaris 11 IPS Package
I'm barely new with Solaris OS and there are still a couple of things I definitely don't get it.
I want to replicate a MySQL installation that someone did in the past in a development server we have. ...
