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 = ...
