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
759 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
1answer
203 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
341 views

Update table with value from another table

I have two tables: AudiTable Id Audi_cod status marke1 1 011 mid Null 2 022 mid Null 3 033 mid Null RenoTable Id ...
1
vote
2answers
43 views

PostgreSQL remove numbers before first comma

I am new to SQL however, I am looking to update a table where column called "scrap_value" and rows contains a set of numbers in an array e.g. {100000,125000,150000,175000,200000} I want to remove the ...
1
vote
1answer
66 views

How can I lock rows FOR UPDATE without returning data over the connection?

I want to do something like this: begin; select * from foos where owner_id=123 and unread=true order by id for update; update foos set unread=false where owner_id=123 and unread=true; commit; The ...
1
vote
2answers
64 views

Why am I getting a deadlock for a single UPDATE query?

I have two processes which execute code like this in parallel: begin; update foos set unread=false where owner_id=123 and unread=true; commit; This results in deadlocks. My understanding of what ...
-1
votes
1answer
48 views

Updating tables in sql

I've made this procedure to update the fields on a table: CREATE OR REPLACE PROCEDURE actualizarEncargadosP( OID_EN2 IN encargadosp.oid_en%TYPE, nombre2 IN encargadosp.nombre%TYPE, apellido2 IN ...
84
votes
1answer
65k 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 ...
1
vote
2answers
37 views

Postgres: update single boolean column on 12 million rows

I have a table with 12 million rows. There are 16 columns on the table, one of which is a boolean. After a certain business process, a large chunk of these rows will have this boolean set to false (...
0
votes
0answers
33 views

Use SELECT query to retrieve UPDATE information

I am confused about inability of using UPDATE query inside SELECT queries. I got this idea, it would be amazing to use queries like follows for elegant reporting purposes My non-working code: SELECT ...
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
45 views

MySQL multi-table update

I'm a bit confused about the proper way to update one table, based on the values of corresponding rows in another table. Here is the query I have: UPDATE features, images SET features....
0
votes
1answer
257 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
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 ...
0
votes
1answer
33 views

Update table with values from another table runs really slow

I have a PostgreSQL table (tab_A) with about 32 million records, and I have a second PostgreSQL table (tab_B) with about 4000 records. tab_B contains 3 fields whose values I intend to pass on to tab_A ...
1
vote
1answer
514 views

Extremely slow MyISAM slave updates

I have 2 slaves where one is working as expected, but the other slave is extremely slow when executing update statements. The insert and delete statements are fast. I used the command check table ...
0
votes
1answer
30 views

in SQL databases does an update rewrite all columns in the row or only those in the SET tuple?

i was wondering how a sql update works in two scenarios: table with 3 columns: a, b, c say the data value for a has to be changed. 1: update on a row setting all columns a, b, c (where b and c have ...
3
votes
2answers
134 views

SQL Deadlocks - Same Stored Procedure Called Concurrently

I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to ...
-2
votes
1answer
76 views

Three updates in order in the same script won't work as intended, why?

I have a table with a field called gender. It's an int. 1 = male, 2 = female. Here's what was done, in the same script UPDATE table SET GENDER = 9 WHERE GENDER = 1 AND (other criteria); UPDATE ...
1
vote
1answer
128 views

How to avoid row locks on both tables joined in an update? [closed]

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
2
votes
0answers
37 views

Why is it faster to SELECT my logical CASE field into a new temp table as opposed to UPDATing the original temp table to SET the field?

I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it #TempTable1. #TempTable1 has a BIT field called ToInclude which is set in a ...
0
votes
1answer
204 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 ...
3
votes
1answer
181 views

UPDATE statement processing records that should be eliminated by WHERE clause

I am getting a very odd error. Consider the table below: CREATE TABLE #MyTable ( Key1 INT , Key2 INT , x SMALLINT , y INT , z INT , a FLOAT , b FLOAT , c SMALLINT , s FLOAT ) -- insert ...
0
votes
1answer
17 views

Does range improve match operation and which runs first, range or match

I am using MySQL version 5.6.10 I have a very very large table large_table and a relatively small table small_table The small_table has only the records of the date 2019-11-20 There is a column I ...
1
vote
2answers
213 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 ...
2
votes
1answer
6k views

Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification

I currently have a table which looks like this: CREATE TABLE "PDPC".collection ( col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass), q1 character varying(10000) ...
0
votes
1answer
38 views

What makes for a good index in this scenario?

I have a pretty big temp table (~5 million rows) that I need to update a few fields on. Here's an example update statement: UPDATE T SET CarModel = CASE WHEN CarModel = '350z' AND ...
0
votes
1answer
192 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 ...
-1
votes
1answer
36 views

Update flag based on max last updated date within in the group

Say for example I have three records: Name Email Group ID Last update date Master Record dinesh.s dinesh.s@xyz.com 10 1 Apr 2019 dinesh.s dinesh.s@...
0
votes
1answer
21 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
223 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 ...
2
votes
1answer
4k 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
469 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
53 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
265 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
65 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
19 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
34 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
119 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
429 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
28 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
47 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
11 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
250 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
30 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: ...