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
1
vote
1answer
1k views

UPDATE with subquery LIMIT 1 returns multiple rows [duplicate]

I am struggling with a query which is randomly returning multiple rows instead of one. I am using PostgreSQL 9.4.12 The query is the following : UPDATE folder as f set locked = 'locked', ...
1
vote
1answer
40 views

Update Select in postgresql

I have three tables, in form of grandparent, parent, and child. By this I mean a foreign key exists in child linking to parent, and another foreign key exists in parent linking to grandparent. Child ...
0
votes
1answer
3k views

Updating column based on conditions from another table

I am updating a column to values based on conditions from another table. I want only the values to be updated for rows where the condition meets. It shouldn't overwrite the rows where the conditions ...
1
vote
0answers
331 views

Update Quantity Available using Quantity from another table

You will demonstrate an update that includes two tables and uses a subquery. Make sure to prove that your update executed correctly by showing data in the tables before and after the update. ...
0
votes
2answers
1k views

MySQL - Why is my BASH Script insert/update query not working correctly? [closed]

Background: I have a MySQL table that stores ip addresses for a very large amount of hosts. It's a simple table (host xyz -> ip1, ip2, ip3). These hosts will write a flat file with it's ip addresses ...
0
votes
1answer
409 views

Version not changed after updating PostgreSQL on CentOS

Actual OS and PostgreSQL versions: CentOS 7.3, PostgreSQL 10.1 to be updated to 10.2. STEPS: Stopping the service: /bin/systemctl stop postgresql-10.service Checking available package updates: ...
1
vote
0answers
107 views

conditional UPDATE race conditions

In a conditional UPDATE like this: UPDATE MY_TABLE SET my_coulmn = "my value" -- many other updates WHERE ... -- many conditions Do I have any guarantee that I will only update those records ...
2
votes
3answers
7k views

UPDATE Trigger with condition

I'm trying to trigger an update on a specific user in my user table. Basically is someone tries to change that user then I want to rollback whatever query was executed and disable the user(using a SP),...
0
votes
1answer
538 views

How do you perform a MYSQL After Update Trigger Insert Loop?

I have 3 mysql tables, one called batches, one called base_formula and one called raw_materials_stock. I want to perform an after update trigger on the batches table to insert information into the ...
2
votes
5answers
2k views

How to speed up update join query between 2 mysql tables?

alright long story short., i have powerful dedicated server. Intel I7-6700K - 64GB DDR4 2400 MHz 1x480GB SSD running mysql server along with nginx,php innodb-ft-min-token-size = 1 innodb-ft-...
0
votes
1answer
30 views

Update a dynamic number of rows with an amount proportionally

Since my English are not perfect I will try explaining with an example I have tableA that I want to update the Amount groupID reservationID Date Amount 26 1 2018-06-01 ...
0
votes
1answer
164 views

(Mysql) Using Memory engine for 4.3 million row table?

My new company stores an intermediate data for our loading process in a table using the memory engine. However a quite simple update (set a.id=b.id) is taking something like six hours to run. I've ...
1
vote
1answer
43 views

Update with joining another table

CREATE TABLE `cities` ( `id` int(11) NOT NULL, `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT ...
1
vote
2answers
90 views

Update statement with strange side effects to data

The problem: I am trying to add some logic to a database refresh job that will de-identify PHI-related fields, and have come across a strange side effect. I am attempting to exclude a group of ...
4
votes
3answers
21k views

How to update column value by adding “1” based on a condition?

I have the following table fields: +----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+------------------------------+-------------+-------------...
2
votes
3answers
951 views

Postgres evaluates syntax error as boolean in Update statement

Recently I came across a bug in code, equivalent to this snippet: create table testing(num int, dt date, istrue boolean); insert into testing values (1, '2018-01-01', True); UPDATE testing SET num =...
2
votes
1answer
2k views

What's the best way to iterate through the table to update a column in each row?

I have a table containing some 3.2 billion rows. It has become necessary to add in a column titled [Daylight] with a datatype of bit. I ended up writing a statement to iterate through each station, ...
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
263 views

Partial updates with functions

My applications rely on PostgreSQL functions to carry out operations on the database. I'm having trouble figuring out a good practice to make partial updates where only some fields are provided. Let'...
3
votes
1answer
5k views

Update nth element of array using a WHERE clause

I have a product table with a jsonb column named 'metadata' in a PostgreSQL 10 database. It's my first time working with documents and Postgres. jsonb values look something like this: { "name" : "...
1
vote
1answer
31 views

Modify all array entries in all records of a table at once

I have a schema that involves an array of timestamps. Due to an import problem all the timestamps are off by one hour (time zone issue). I'd like to update all the timestamps in all the records with ...
1
vote
1answer
315 views

What's the duration of U lock

As far as I know, SQL Server holds X locks for the duration of a transaction no matter which isolation level is in use. What about the U lock? If a transaction reads 100 records in an UPDATE statement ...
0
votes
0answers
143 views

Slow updates (26seconds!) with mysql, likely fix to use innodb_flush_log_at_trx_commit = 0?

I have periodic slow queries in a rails site. Happens mostly on updates / inserts. This is reporting that it is taking 26 seconds for an update to occur. I see this in the logs, so, I'm not 100% sure ...
0
votes
2answers
1k views

What is best practice for confirming success of Update queries

If I am doing a SQL Update query that affects, for instance, 1000 rows, how do I check that the query worked as intended and didn't hit any strange edge cases, (short of examining each row visually ...
0
votes
1answer
1k views

performance of Replace vs If exists update else insert vs update if row_count()=0 insert

Structure of my table is: Id int(11) NO PRI auto_increment Mobile varchar(10) NO LoanAmount int(11) NO I want to UPDATE if existing ...
1
vote
2answers
2k views

Update a table using summed values from another table in MySQL/mariaDB

I was trying to use the query below to update the ORDERS table by making the O_TOTALPRICE column up-to-date when the LINEITEM table is modified. And I was using a standard TPC-H database. UPDATE ...
5
votes
2answers
3k views

Apply the latest Service Pack and Cumulative Update

I have given an RTM version of SQL Server 2014 and I need to apply the latest SP (currently SP2) and CU (currently CU8). Do I need to apply SP1 first? Or can I straight move to SQL Server 2014 SP2 CU8 ...
0
votes
0answers
94 views

Updating a Table Based on Latest Record From Other Table

I have two tables as follows: Table 1: ID Employee_ID Date 1 1 1-1-2017 2 1 3-6-2017 3 2 4-5-2017 4 2 6-6-2017 5 3 ...
0
votes
1answer
11k views

SQL Server : Merge and update multiple rows across columns

I am trying to consolidate data in one table and merge it into another. The data in the source destination table is as follows : name |dob |city |occupation --...
0
votes
2answers
31 views

What option we can disable temporary in PostgreSQL make database update faster

I want to update 3 million records within some seconds. i have tried doing below things ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ; ALTER TABLE ...
0
votes
1answer
16 views

More efficient to do SELECT and compare in Java or DELETE and INSERT

I am hitting a REST API to get data from a service. I transform this data and store it in a database. I will have to do this on some interval, 15 minutes, and then make sure this database has latest ...
1
vote
1answer
169 views

PostgreSQL and days that don't exist? [closed]

While updating data in my table i am getting below error. date/time field value out of range: "2017-2-29" I want to skip this error and want update statement to continue further. How can we do ...
3
votes
1answer
36 views

Does HOT Update re-arrange existing rows on a page to create a contiguous space?

For example, on a 8kb page: 4 rows are created, each 2kb Row 3 is deleted Row 1 is updated to a size of 3kb There is no contiguous chunk of 3kb space on the page, so what happens? Do rows on a page ...
0
votes
1answer
59 views

How to enhance speed of UPDATE statement

Out of practice , some tips on indexes efficiency appreciated. Creating test data ... changing consultant name on a large table using an update statement. one of my tables has 10 rows, second table ...
1
vote
1answer
316 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
775 views

Postgres update with replace

I am doing select which is: SELECT replace(field_1, E'<div class="ad-content"><div class="ad-title">publicidade</div><div id=\'div-gpt-ad-1426519214388-1\'> <script type=\'...
5
votes
2answers
5k views

SQL Server Concurrent Inserts and Deletes

From: SQL Server 2014 Concurrent input issue Follow up question: How do we Delete and Reinsert rows in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc? Do we ...
0
votes
1answer
81 views

SQL Server Concurrent Insert/Update and Storing Data

From: SQL Server 2014 Concurrent input issue Follow up question: Table Orderline stores information we receive from shops. OrderLineID is the unique, primary key in the table, since orders have ...
1
vote
1answer
6k views

If Exists before Update or Delete?

Before I write an update statement, should I check if it exists? Someone told me to do this So it avoids uselessly writing to the transaction log for value that already exists, And only takes a ...
2
votes
2answers
77 views

in place updates causes forwarded records

I am well aware what a forwarded record within a heap is. Since I want to keep forwarded records at 0, we decided to update only on columns that could not be extended. Recently on my system I ...
0
votes
1answer
993 views

Update Query said “ORDER_DETAILS”.“QUANTITY”: invalid identifier [duplicate]

Update Query said ORDER_DETAILS.QUANTITY is an invalid identifier. I check and there is a table named ORDER_DETAILS with a field named QUANTITY. Error at Command Line : 14 Column : 57 Error report ...
0
votes
1answer
796 views

How to convert an Update a query from MS Access to Oracle SQL Software [duplicate]

I have an update query below written in Microsoft Access and I would like to convert that to Oracle SQL Developer 17.2.0.188. There is also an image in the attachment showing the query expression. ...
3
votes
2answers
59k views

How to update multiple columns of multiple rows in one SQL statement

In my SQL Server database I want to update columns of multiple rows. I can do it for one row only: UPDATE theTable SET theColumn = case WHEN id = 1 then 'a' WHEN id = 2 then 'b' WHEN id = 3 then 'c' ...
2
votes
1answer
2k views

Update SQL Server 2016 from RTM to SP1

I have SQL Server 2016 RTM installed and would like to update it to SP1. When I run an SP1 cumulative update, it fails because there is nothing to update. Presumably this is because it needs SP1 to be ...
4
votes
3answers
2k views

Is there anyway to UPDATE a series of columns on a row with a composite type record/row?

Take a table foo, CREATE TABLE foo(a,b,c,d) AS VALUES (1,2,3,4); I can insert into foo with a row type.. INSERT INTO foo SELECT (foo).* FROM foo; INSERT INTO foo SELECT (v).* FROM ( ...
2
votes
2answers
2k views

Update column with another table's value

I have two tables, users (with columns email, name, id) and user_details (with columns username, address, details). Currently the username in user_details is populated with a mix of ID's and Email's. ...
0
votes
3answers
4k views

Update values in one table based on contents of another

Software Name: Oracle SQL Developer Version 17.2.0.188 I have three tables: orders, order_details, and products. I wanted to run a query to update units_in_stock in the products table based on ...
1
vote
2answers
47 views

Audit Trail trigger can't detect if dates are the same

I know this is a common problem with SQL language triggers, but I just cannot seem to get this right. Basically I'm trying to write an "Audit Trail" trigger for all of my fields. I'm using an IF ...
14
votes
1answer
455 views

Unique index updates and statistics row modification counters

Given the following table, unique clustered index, and statistics: CREATE TABLE dbo.Banana ( pk integer NOT NULL, c1 char(1) NOT NULL, c2 char(1) NOT NULL ); CREATE UNIQUE CLUSTERED ...
2
votes
2answers
40 views

Is clustering a table beneficial, given this update pattern?

I have a table essentially equivalent to this example: create table my_table(i integer, x text, y integer, z integer); create index idx_i_x on my_table(i, x); create index idx_i_y on my_table(i, y);...