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.
770
questions
0
votes
1answer
19 views
Update a record whilst creating a link to a parent record
Someones account was deleted from a system we have. They created a new account for her and moved on. But it turns out she wasnt deleted from SQL - when they delete in the app, the record is marked as '...
1
vote
2answers
203 views
MySQL - Deduct from two columns with a remainder
I have the following table:
Id, balance, otherBalance
=============================
1, 100, 50
I would like to create an update query that receives a parameter 'amount' and does the ...
0
votes
1answer
17 views
MongoDB conditional set [closed]
I'm using MongoDB + Mongoose + Node.JS
App logic: We have a game similar to minesweeper. User selected cells in game field store in fields: Array, and bombs locations store in bombs: Array.
When ...
2
votes
1answer
3k views
What is a SQL Server “GDR”?
Microsoft provides hosts two repos,
https://packages.microsoft.com/repos/mssql-server-2017-gdr-xenial/
https://packages.microsoft.com/repos/mssql-server-2017-xenial/
What does GDR stand for in that ...
1
vote
1answer
5k views
PostgreSQL, UPDATE CASE statement uses huge amount of space
I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well.
I am using ...
1
vote
5answers
176 views
Updating multiple values at a time
I have been given the task to update a few rows in adatabase. There are two columns in which one is ID and the other is CURRENCY. I have updated these rows using the update statements that follow:
...
0
votes
2answers
52 views
Read uncommited does not work in UPDATE WHERE NOT EXISTS
I'm not able to force SQL Server (2016) to respect values updated in the same statement.
What I need to do is to update old values (column a in table #t in demo code below) with mapping table (#u) ...
-1
votes
1answer
254 views
INSERT and UPDATE in Trigger
I have created a trigger so I could insert every row in a backup table
CREATE OR REPLACE TRIGGER xyz AFTER INSERT
ON table_1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO table_2
(...
1
vote
1answer
43 views
Update dynamic column names using keys from jsonb argument
I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, ...
-1
votes
1answer
124 views
Update multiple meta_value with single query
Here is a similar structure of a table that I am working on.
|--ID--|--user_id--|--meta_key---|--meta_value--|
|--1 --|--444444---|--first_name-|------NULL----|
|--2 --|--444444---|--last_name--|-----...
3
votes
1answer
16 views
Select and Update MAC Addresses to Same Format
I have a mysql database with a macaddress column, this contains thousands of mac addresses in various formats; uppercase, lowercase and with colons. See below for an example;
+-------------------+
| ...
0
votes
1answer
31 views
How to count multiple occurences on the same date
my question is related to the table down below, where I have to count multiple occurences of "Hours" if they happen on the same "Date" by the same person.
CREATE TABLE [dbo].[Time](
[GUIDTime] [...
0
votes
1answer
92 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
votes
1answer
368 views
MySQL 5.7 UPDATE slow due to not using index
We have an issue similar to this. Only, the solution there won't work for us as:
We don't use multi-table JOINS, but UPDATE … WHERE `userid` IN (<list of 10k ids>);
We currently "only" have the ...
0
votes
0answers
22 views
Fill in Ending Balance from previous months balance
I need to fill in the Ending Balance of each Year / Period where it is 0 (zero) but a previous period for that year and company has an ending balance - basically carrying forward the ending balance ...
0
votes
1answer
18 views
how mongo db does locking for updates/inserts?
I have many data for each user that with write heavy and many for each separate users that there is not any relation between user's data; so there is two options for me to store them:
1. keep all user'...
2
votes
1answer
45 views
UPDATE column to MIN of a SELECT result set - what went wrong?
I've got the following scenario:
I've got
a table of assets with standard columns
a table of hardware assets with extension columns
a history table where all changes to an asset are stored with ...
0
votes
2answers
21 views
Select data from table join or UDPATE?
I have a question regarding PostgreSQL DB performance. I am given a CSV file with a lot of rows (let's say around 1 million rows) that represents updates to a database table (let's call it MY_TABLE). ...
0
votes
1answer
10 views
Update Mysql records using Same table but Based on same table record
I have table in which i want to update records based on it's 'wishlist_id' value.
Sample format of table records.
Expected table record after update.
So can you help to update this record using ...
1
vote
2answers
57 views
Update all values for given key nested in JSON array of objects
I have a jsonb column data in a Postgres table my_table. It contains the following data:
[
{"id":"1","status":"test status1","updatedAt":"1571145003"},
{"id":"2","status":"test status2","...
0
votes
2answers
46 views
Editing all columns
I have a db table with more than 200 rows (table name: ports).
There is this column (port name). The data in the column is like: aaa 1/15.
I want to edit all the data in this and turn the aaa 1/15 ...
0
votes
3answers
25 views
Update column with the value of two columns multiplied
I want to update ColumnA with the value of ColumnB x ColumnC. Can someone please help? I have tried the following:
UPDATE database.table
SET ColumnA = ColumnB * ColumnC;
Error message as follows:
...
1
vote
1answer
319 views
PL/SQL Insert Update Before Trigger
I have two tables ProjectsTable and ExpenseTable given following and a trigger ExpenseSum which update ProjectsTable when a value is inserted in ExpenseTable table. Now, I want to modify Trigger so ...
0
votes
1answer
20 views
Update a column based on another's table column value
I have a table Users
+----------+----------+
| ID_EMP | isActive |
+----------+----------+
| 100 + 1 +
| 101 + 1 +
+----------+----------+
and a table UsersInfo
+----------...
2
votes
0answers
123 views
SQL Server 2016 SP2 CU9 - WFC Failover failing
I have updated my SQL Server 2016 on one of my Windows Failover Cluster nodes successfully. When I attempted to failover one of my SQL instances onto the newly patched node, SQL Server service fails ...
4
votes
2answers
14k views
Change SELECT DISTINCT to UPDATE DISTINCT
How can I modify my SELECT DISTINCT query to an UPDATE DISTINCT query?
It is important that it only updates the distinct records, as there are multiple records associated with each [...
-2
votes
1answer
41 views
ON DELETE / ON UPDATE doubts
I was looking at database past exam from the school that I attend, and I have a question about ON DELETE and ON UPDATE.
The question in the past exam was:
Show the contents of the tables after ...
0
votes
1answer
185 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
2answers
27 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
1answer
536 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
0answers
29 views
Size and performance different on UPDATE with/out intermediate CTE
I've got two tables with an indexes id column in common. I'm trying to add a new column in the target table using the value of a column in the source table. The two tables are rather large with couple ...
1
vote
1answer
19 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
0answers
22 views
Creating a trigger which stores updated value with old value in separate table
I'm trying to create a trigger which adds an old value to a table with the new value next to it.
For example, if someone were to update their address, I'd like the trigger to add the old address to ...
2
votes
1answer
2k 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 ...
0
votes
1answer
28 views
Need help with correlated subquery
I have two PostgreSQL tables that each contain a column called date_of_incident.
They each also have a column called incident_id and this is how the tables are related.
I want to update all rows of ...
0
votes
2answers
31 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
2answers
44 views
How to use TO_NUMBER function to update columns of a table Using oracle pl/sql
Hope you're doing well.
I have a table with this structure :
Table1(Cust_No Varchar2(50),
First_Name Varchar2(50),
Last_Name Varchar2(50),
Error_Code number(1),
...
0
votes
1answer
48 views
How to loop through all table rows and perform subquery using values of same row?
I have a table structured as follows:
| reference | valA | valB | xml |
1 NULL NULL <xml stuff>
3 NULL NULL <xml stuff>
4 NULL NULL ...
0
votes
1answer
29 views
Add a suffix to duplicates on PostgreSQL
Not sure about the term "undouble", I am just trying to add a unique index on a column but it contains doubloons, so I need to update data first.
Figure this data:
BEFORE update
1;foo
2;foo
3;foo
4;...
0
votes
1answer
196 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 ...
1
vote
1answer
22 views
MySQL uses range and where on UPDATE with the primary key
I have these simple SELECT and UPDATE requests on MySQL using the primary key, there are about 13 million rows in the table.
When running an EXPLAIN on both, I notice that the UPDATE one does not use ...
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
47 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
715 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
30 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 ...
