We’re rewarding the question askers & reputations are being recalculated! Read more.

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
2
votes
1answer
949 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 ...
-2
votes
1answer
57 views

Simple Trigger Is Not Created in Mysql

I'm creating a trigger in mysql, but even with the correct syntax and the query being accepted the trigger is not created. If anyone can help. The following is the sql: DELIMITER $ CREATE TRIGGER ...
-1
votes
1answer
366 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 ...
1
vote
2answers
3k 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
1answer
668 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
242 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 ...
-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 ...
0
votes
2answers
110 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 ...
2
votes
2answers
194 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
1answer
144 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
37 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 ...
1
vote
3answers
44 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 ...
4
votes
2answers
163 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 ...
2
votes
1answer
212 views

Update MySQL user table of active/inactive members based on array

I have a MySQL table of unique users. I have an 'active' boolean column (0 or 1) to denote whether the user is currently subscribed to the site. The primary table will hold the 'active' boolean column,...
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 ); ...
5
votes
2answers
191 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
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 ...
0
votes
2answers
141 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
963 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
394 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 ...
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 | -------------------+--------------------------+--...
3
votes
1answer
462 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 ...
0
votes
3answers
116 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, ...
1
vote
1answer
152 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 ...
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
320 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
89 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 ...
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
404 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
11k 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-...
0
votes
2answers
84 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
397 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 ...
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
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-...
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:...
1
vote
2answers
559 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 ---------...
0
votes
1answer
1k views

SequelizeDatabaseError: cannot execute UPDATE in a read-only transaction

I'm currently using Sequelize, NodeJS and PostgreSQL. I have users who update their accounts password on first login. Well, today I had this error appear after 3-4 successful logins. What does this ...
1
vote
2answers
201 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
2answers
73 views

Update a table with data from another table

I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. ...
0
votes
0answers
178 views

Process an OLAP Cube as a daily job takes too long using `ProcessFull` option

I have two cubes which updates in sequence. These takes very long to update, upwards of 5 hours due to their sizes. I have a SQL agent job which updates the cubes. The job has type SQL Server ...
1
vote
1answer
5k views

Oracle update statement using with clause takes for ever

I am trying to write an update statement, which utilizes the result of a series of aggregations of with statement as below. update final_table set(destination_col1,destination_col2)= ( select c4,c5 ...
0
votes
0answers
420 views

Two versions of Postgres installed, how do I clean this up?

If I try to enable PostGIS with CREATE EXTENSION postgis; the error I see indicates that psql is looking for postgres.control in a 9.4 directory: ERROR: could not open extension control file "/usr/...
-1
votes
1answer
3k views

Update a table using values from another table

I have two tables with structure Table1 ------ No | Name | IDNo | HouseNoBlock Table2 ------ IDNo | HouseNo | Block I am trying to update the HouseNoBlock Column of first table with value ...
-1
votes
1answer
341 views

SQLITE3 Commandline Tool SQL UPDATE Query syntax problem

I have two databases with identical table structures Dest.db = main Source.db = src the table is called colorramp structure of colorramp is ID | name | xml | favorite main.colorramp needs ...
7
votes
2answers
3k views

Simple SQL CTE update

I am a little stumped with this CTE update stmt: DECLARE @a TABLE (ID int, Value int); DECLARE @b TABLE (ID int, Value int); INSERT @a VALUES (1, 10), (2, 20); INSERT @b VALUES (1, 100),(2, 200); ...
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', ...