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.
773
questions
0
votes
1answer
1k 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 ...
2
votes
1answer
32 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
48 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
44 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
27 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
39 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),...
3
votes
1answer
718 views
Update Table Query: Automatically handle “Enter Parameter Value” prompts?
EDIT: Here are two database Files that demonstrate an applied example of the problem:
Example_DBs.zip (Zipped due to 2 link limitation.)
Note: The missing Field in "Not Working.mdb" is intentional....
0
votes
3answers
31 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
6k views
How to access row value from UPDATE sub query function?
I would need to use UPDATE like this:
update offer set (bank, bank_id) = (cost, gid)
from (
select cost, osm_points.gid from pgr_kdijkstraCost(
'SELECT gid as id, source, target, walk_cost as cost ...
0
votes
0answers
10 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 ...
7
votes
1answer
5k views
Update by iterating table in batches faster than whole table in PostgreSQL
I have a table with approximately one million rows.
It is being used in production and I ran an UPDATE which covers ~95% of those rows.
After five hours I cancelled the request because it was taking ...
0
votes
0answers
134 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
42 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 ...
1
vote
2answers
30 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
67 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
80 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
38 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 ...
0
votes
1answer
41 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
32 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 ...
4
votes
2answers
2k views
Adding hstore entries to an uninitialized (NULL) column
I got bitten by this "feature" recently.
If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error.
Is this expected?
create table ...
3
votes
2answers
12k views
Innodb UPDATE performance
We recently switched one of our tables to innodb and now we are experiencing very slow UPDATE execution times. An update which used to take 0.010-0.030 seconds can now take over 70 seconds. Some ...
-1
votes
1answer
33 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
23 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
2answers
245 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
...
0
votes
1answer
40 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....
2
votes
0answers
21 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 ...
1
vote
1answer
113 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 ...
13
votes
1answer
2k 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....
2
votes
1answer
2k 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
3answers
10k views
How to update complex jsonb column?
I have a table with following definition:
create table json_test (
filter_data jsonb);
and i insert to it values like this:
'{"task_packets": [
{
"state": "PROCEEDING",
"...
0
votes
1answer
39 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 ...
1
vote
1answer
25 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 ...
7
votes
1answer
124 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 ...
7
votes
1answer
31k views
Oracle: Non key-preserved table should be
I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
0
votes
1answer
16 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 = ...
0
votes
3answers
2k views
MySQL update tables in batches
I want to update my MySQL table with batches.
The table has 10000000+ records, so I want to update first 1000 rows, once it is done then update 1001 to 2000 rows. I need to make it as a loop.
2
votes
1answer
241 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
32 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,
`...
82
votes
1answer
63k views
Postgres UPDATE … LIMIT 1
I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't ...
2
votes
0answers
110 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 (...
0
votes
1answer
27 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
31 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
36 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 ...
10
votes
2answers
84k views
Update column with data from another table
I am working on complicated problem, but I will simplify it to this problem.
I have two tables
A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]
and I want to update the ...
1
vote
1answer
463 views
Numbering rows consecutively for a number of tables
I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to aid,bid,...
32
votes
5answers
33k views
UPDATE performance where no data changes
If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to ...
0
votes
1answer
325 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;
+-----+--------...
11
votes
2answers
5k views
Direct the OUTPUT of an UPDATE statement to a local variable
I would like to do this :
DECLARE @Id INT;
UPDATE Logins
SET SomeField = 'some value'
OUTPUT @Id = Id
WHERE EmailAddress = @EmailAddress -- this is a parameter of the sproc
Is this even ...
7
votes
2answers
103 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"')
...
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 ...
