Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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.

0
votes
1answer
25 views

mysql update based on select result, How combine?

Here are two SQL statements. One is an select statement and the other is a update statement. I want to combine them. Select SELECT * FROM test WHERE `id`='1' Update UPDATE test SET `select_count`=...
1
vote
1answer
41 views

Innodb updates records with delay

I have two tables in my database like these: CREATE TABLE `main` ( `id` int(11) NOT NULL AUTO_INCREMENT, `type` int(11) NOT NULL, `status` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB ...
0
votes
0answers
22 views

Do any of the PostgreSQL releases for Windows support automatic updates?

PostgreSQL has two distributions for Windows, EnterpriseDB BigSQL Do either of these Windows distributions support automatic updates for the term of support? Something that looks for the updates ...
1
vote
1answer
69 views

Which is faster to update thousands of table rows?

With stored procedures in PostgreSQL v10.1.2, which is the fastest way or which is better: to check if rows exists and then update or try to update directly with the possibility that no rows matching ...
0
votes
1answer
14 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
38 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
18 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
votes
0answers
18 views

Updating rows with duplicate id but different data, conditions not stopping update

I'm currrently trying to update some alarms by colors based on specific data UPDATE table1 SET color = CASE WHEN accountnr IN (Select costumernr from table2 WHERE (STATUS = 1 OR ...
0
votes
1answer
29 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
29 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
26 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 ...
1
vote
2answers
51 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
61 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
42 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 ...
0
votes
1answer
28 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
2answers
41 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 ...
0
votes
1answer
66 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
33 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
31 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
30 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
59 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
28 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
13 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
40 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 ...
3
votes
2answers
69 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
61 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
68 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
98 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....
1
vote
0answers
12 views

Splitting Multiple items with single name into Multiple items with distinct name

I have a database in which a particular Resource of the same kind is available in multiple numbers. eg: Machine 1 I have 10 Nos, Machine 2 I have 5 Nos. I have to assign each machine a distinct name....
0
votes
2answers
44 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
69 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
0answers
35 views

Divide and Update the Hours in SQL Server

I have two tables: 1. ForemanReports having columns ID, Date etc. 2. FRToMainsInstallation having columns ID, ForemanReportID(Primary ID of ForemanReports Table), Hours. ForemanReports: ID &...
0
votes
1answer
26 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
80 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
80 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
67 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
30 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
31 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
61 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
168 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
38 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
109 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 ...
0
votes
1answer
247 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
82 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 ... (...
14
votes
3answers
2k 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
477 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
36 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
42 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 ...
2
votes
2answers
398 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 = ...