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
53 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. ...
0
votes
1answer
44 views

Dealing with Oracle ARCHIVELOG during huge updates

It's required to update columns with metadata globally in all rows of all tables in DB. Let's say every table has a column MY_META and the aim is roughly update ANOTHER_TABLE set MY_META = 'HELLO' ...
0
votes
1answer
165 views

PostgreSQL Update Column From Lookup [closed]

I'm trying to replace a column with a lookup from another table. Seems simple, but I'm thinking PostgreSQL is getting in the way for some reason. I've seen that some syntax that evidently doesn't ...
1
vote
1answer
341 views

Trigger to cascade updates to a second table

In my PostgreSQL 9.6 database, I am trying to update 2 fields in table2 with a trigger when table1 is modified. Function: CREATE OR REPLACE FUNCTION schema.maj_tbl2() RETURNS trigger AS $BODY$ ...
0
votes
1answer
873 views

Concatenate into Field in Update

I am trying to write a Stored Procedure which updates a quantity data column in one of my tables, and then takes a field set for random notes and concatenates the update information. This is what I ...
2
votes
2answers
208 views

Why is column value going negative even with checks

I have a simple MySQL table with 3 columns id, balance and locked. I have a requirement where the locked column's value must be increased by x, sometimes by multiple processes running at the same time,...
1
vote
2answers
4k views

How to limit rows in PostgreSQL update statement

My table employees contain more than ten million data. i want to update 10k rows in commission_pct column where commission_pct is null and set zero(0) value. in oracle I can easily solve this by ...
1
vote
2answers
97 views

How to only update part of a column based on conditions?

This is a continuation of Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row" , however i felt the question was too different to follow ...
2
votes
1answer
991 views

Updating a table with duplicate id's while avoiding “ single-row subquery returns more than one row” [closed]

I'm currently making SQL that'll update data from 1 table, based on data from another table for my company update table1 set column1 = (SELECT color FROM table2 Where ...
-1
votes
1answer
42 views

How to link two columns under new column in the same table?

I want to link two columns under new column in the same table. For example: in my database there is an ID of the employee and there is another ID for his manager in the same table. Also, in the same ...
1
vote
1answer
729 views

Updating a large table in sql server

I have a wide table (80 columns) with 70 million rows in it. I can currently trying to set a foreign key across all rows within the table. Approach one An update statement over the entire table such ...
-3
votes
1answer
277 views

Update same values in the field with different values in MySQL

My Table is in the following format in MySQL: CREATE TABLE table11 (id int,col1 float,col2 int); INSERT INTO table11 (id,col1,col2) VALUES (1, 1.1, 1), (2, 1.2, 1), (3, 1.3, 1), (4, 1.4, 1); I want ...
0
votes
2answers
114 views

Best approach to delete and update a big amout of rows in MySQL

I'm currently developing a REST API that connects to a MySQL database and does a bunch of queries. In one of those queries I happen to have to delete a bunch of rows from a table and each of those ...
1
vote
1answer
161 views

How to check with update trigger if only one column was updated postgresql

I have a table with many columns and one special column that indicates if is synchronized or not. When an update occurs in the other columns I processed and update a 'log' table, this updates comes ...
1
vote
0answers
38 views

Optimization Technique For Update Query in MySQL 5.1.73-log

I am trying to optimize an update query on InnoDB table in MySQL 5.1.73-log. As per my understanding we can not use EXPLAIN on update query for 5.1 version. So what will be the alternate course of ...
1
vote
1answer
13 views

Return an error where the ID matches, but the owner_id doesn't

I've seen a common pattern like this used to update a record: UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234 However, what if there is a requirement that a non-existentent ID is ...
4
votes
2answers
171 views

How to make a column mandatory in every Insert / Update (not meaning default null)

Following table: CREATE TABLE kontrolle.negativtext ( id serial NOT NULL, ausschluss character varying(255) NOT NULL, nur_domains character varying(255)[] NOT NULL DEFAULT '{}'::character ...
1
vote
3answers
45 views

What's the correct syntax for this Postgres query in MySQL?

I have a query which works well in our PostgreSQL db, but we now need to run our app on a MySQL DB as well. The query takes an array of IDs and positions, and updates each record with given ID with ...
5
votes
2answers
197 views

Query Plan question

I've created a varbinary hash to check changes between 2 tables. Here is the execution plan, I'm a bit stumped on the indexing, or indeed if there is a better way of writing this. https://www....
2
votes
1answer
137 views

Recommended settings for a small table with high update rate [closed]

I have a small table which can be explained as follows: create table mytab ( primary_key_column number, a number, b number, c number, d number, e number, f number ); ...
2
votes
0answers
427 views

Bulk Updates (inserts, update) and check constraints

Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the ...
1
vote
2answers
1k views

How to use FOR UPDATE with a view that is built using LEFT JOIN?

Assuming a simple table, e.g. \d+ task Column | Type | Modifiers | Storage | -------------------+--------------------------+--...
0
votes
1answer
1k views

Array of strings when updating a field

Unintentionally discovered that following query works in PostgreSQL: UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1; I'm passing an array of strings to field and the ...
0
votes
2answers
155 views

Is it possible to make a SELECT wait until an ongoing UPDATE finishes?

Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might ...
1
vote
1answer
1k views

Postgresql update multiple rows and skip row on error

I have a query which updates single column of multiple records using column value from another table. For example below are the tables-- Table A +-----------------+---------------------+------+ | id(...
0
votes
1answer
417 views

Updating multiple tables with similar structure in one query in mysql

I have multiple tables "100+", with the exact similar structure, yes I know I probably should not have it this way but it is what it is now. What I want to do is, update all tables in one query ...
3
votes
1answer
475 views

How to handle large database updates with node js [closed]

I have a program which is time based. The users have all day to enter data that will affect their score, however if you haven't entered anything by midnight then your score is negatively affected. I ...
1
vote
1answer
2k views

MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

I had a table with ~0.5M rows and tried to execute a simple query, something like: UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999 but the result was ERROR 1297 (HY000)...
0
votes
3answers
117 views

what columns to include in the index to improve the performance of the following update?

I have the following update: UPDATE dbo.fees SET amount = @amount, currencyCode = @currencyCode, feeType = @feeType, countryCode = @countryCode, gatewayCode = @gatewayCode, ...
13
votes
2answers
36k views

Trigger to UPDATE after UPDATE?

I want to make a trigger to record the time of any update as: CREATE TRIGGER col_update AFTER UPDATE ON col FOR EACH ROW BEGIN UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id END The ...
1
vote
1answer
160 views

Update value with nested subqueries

I am working in Oracle and trying to update a value in tableA to = 'Y' based on info from two other tables (B and C) I have tried something like this which does work if I have only the first select ...
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 ...
1
vote
0answers
32 views

Correct sequence of MSSQL Server updates [duplicate]

What is the correct sequence to follow when updating MSSQL Server to a new service pack? For example, if I have an instance of MSSQL Server 2016 RTM and I wish to update to SP2, do I need to first ...
2
votes
1answer
337 views

Update xml based off datatype

I need to update a value in my xml datatype column, currently I am able to pull specific values using Select AllUserData.tp_ColumnSet.value('nvarchar13[1]', 'varchar(max)') AS AssetId... My data in ...
1
vote
2answers
1k views

UPDATE in combination with nested SELECT queries and LEFT OUTER JOIN

I have a problem with an UPDATE statement that contains a nested SELECT statement in combination with LEFT OUTER JOIN statements. Situation: In our database there is a new column added to all people ...
1
vote
0answers
90 views

Database Behavior while executing UPDATE and INSERTs on the same table

My gear: Pentaho BI Server 8.0 AS400 V7 My scenario: I have two processes that sometimes run at same time in the same table. This table has about 600M rows, so, an update can take long time to ...
1
vote
1answer
2k views

Help Writing an UPDATE Statement With JOIN

I am new to SQL Server and am attempting to write an UPDATE statement that includes a JOIN. Essentially this is what I am trying to write, but obviously this is invalid syntax. In SQL Server 2008 R2 ...
12
votes
3answers
52k views

UPDATE table based on the same table

I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same ...
1
vote
1answer
6k views

Update using select in DB2

I have 2 tables Table A address_id email 12341 aba@mail.com 12342 abb@mail.com 12343 abc@mail.com 12344 abd@mail.com 12345 abe@mail.com 12346 abf@mail.com 12347 ...
-1
votes
1answer
420 views

UPSERT in postgres stored procedure

Postgres already has a way to do "upsert" but I don't like sending these long queries, I wonder if it could be implemented as a stored procedure which would accept json and perform INSERT INTO ... (...
15
votes
3answers
12k views

SQL Server : How to disable trigger for an update only for your current session?

I am working on SQL Server 2008 R2. I have a table benefit which has a AFTER INSERT, UPDATE trigger named tiu_benefit. I want to write an UPDATE statement for this table to update 1 row but I dont ...
0
votes
2answers
5k views

UPDATE vs INSERT INTO… ON DUPLICATE KEY UPDATE

Alright so I have this table in which until now I was using the following queries to UPDATE else do an INSERT: $db->query("UPDATE ulogs SET invalid = invalid + 1 WHERE uid = 666 AND date = '2018-...
1
vote
1answer
32 views

Referencing specific columns using a variable range [closed]

I have to shift 20+ columns over one to the left, for a set of rows. Is there a way to do this without manually typing each column name? I know how to get the data for the specific rows. For example:...
3
votes
2answers
416 views

Update query with two different where conditions

I would like to set status to false for all rows except one, which should should be set to true. How can I achieve that? Here is the query I tried: UPDATE students set status = false where status = ...
0
votes
2answers
85 views

How to append zeros to substring Datetime values in sql table?

I need to change a DateTime to a new format by appending zeros to dates less than 10. DateTime column is nvarchar(MAX) Currently: - April 8 2018 4/8/2018 00:00:00 AM New wanted format: 4/**08**/...
0
votes
1answer
402 views

Batch update timestamp records to correct timezone

I'm looking to batch update some timestamp records in postgresql. My app was storing timestamps to the timestamp column (without timezone) in the the wrong timezone (BST). I only noticed a while ...
0
votes
2answers
1k views

How to update “sorting_order” column of other rows when changing one?

I need to set a custom order sequence for categories when adding new ones, and changing existing ones. I believe the code and sql query will be the same in both scenarios. In my webshop_categories-...
6
votes
2answers
6k views

Which is faster? Multiple update queries in a cursor loop, or a single query?

Which is faster? Multiple update queries in a cursor loop, or a single query? For example, in this case, which is faster? This query is in a trigger. Multiple Query in loop DECLARE done INT DEFAULT ...
1
vote
1answer
3k views

How to UPDATE NULL values by MAX()+1?

I want to assign values to null cells of a column as UPDATE table 1 SET number = MAX(number) + 1 WHERE number IS NULL but mysql return error Invalid use of group function.
1
vote
2answers
570 views

Update using the value from the closest row

My table looks like this: ----------------------------------------- id | name | housename | houseno ----------------------------------------- 1 | John | John's House | 2/22 ---------...