All Questions
29 questions
7
votes
3
answers
46k
views
How to UPDATE from JOIN with GROUP BY
In a SELECT query of
SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0
How can I turn this query to UPDATE as
UPDATE b.number ...
- 4,531
5
votes
2
answers
292
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....
- 85
4
votes
5
answers
5k
views
How to update table records in reverse order?
I've a table Student
Id Name Mark
1 Medi 10
2 Ibra 15
3 Simo 20
and I want to update it, where I want to reverse it in descending order only Name and Mark and keep Id in its order:
...
- 292
4
votes
1
answer
240
views
An UPDATE runs unexpectedly on MySQL 5.7 whereas it runs as expected on prior versions
I use a column called sequence on tables where I have to re-sort blocks of rows into an arbitrarily order.
CREATE TABLE `tkmemberstage` (
`ID` bigint(22) unsigned NOT NULL AUTO_INCREMENT,
`...
- 143
4
votes
1
answer
1k
views
Select and Update MAC Addresses to Same Format
I have a mysql database with a macaddress column, this contains thousands of mac addresses in various formats; uppercase, lowercase and with colons. See below for an example;
+-------------------+
| ...
- 177
2
votes
3
answers
277
views
Query to update one column of a table based on a column of a different table
I have two tables with below structure :
Create table Customer_info
(Customer_num varchar(50),
Customer_Branch int);
Create table Customer_Transaction_Info
(Customer_num varchar(50),
...
- 1,486
2
votes
1
answer
2k
views
Do UPDATEs block SELECTs?
I need to query a frequently updated record, does it wait until all updates finish?
/updateUser/1
Many requests are sent
UPDATE users SET count = count + 1 WHERE id = 1 LIMIT 1
/getUser/1
While the ...
- 77
2
votes
1
answer
149
views
find 16,000 words and replace with ##
I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i ...
- 21
1
vote
1
answer
4k
views
Can I retry every SQL query in case of a deadlock exception (MSSQL)
Is it always safe to retry an SQL query in case of a deadlock exception? Can this lead to unexpected behavior or executing an operation twice? I think a deadlock exception always means a rollback even ...
- 13
1
vote
1
answer
2k
views
Can I split text from one column into to two existing columns in My SQL [closed]
SELECT `Appointment`,
SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 1), ' ', -1) AS Date,
SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 2), ' ', -1) AS Time
FROM go_applicants
...
- 13
1
vote
1
answer
1k
views
Receiving error "invalid identifier" while trying to update a table in Oracle
I'm trying to transform a Merge query into an Update query for getting better performance but I could not succeed.
Tables structure:
create table TEST_FILE_MERGE
(
customer_num NUMBER,
...
- 1,486
1
vote
2
answers
2k
views
ON DUPLICATE KEY UPDATE in INSERT INTO SELECT with GROUP BY
I have the query of
INSERT IGNORE INTO t2 (ID,P1,P2,Items)
SELECT ID,P1,P2,SUM(Items)
FROM t1 GROUP BY ID,P1,P2;
Since I need to run this query frequently to inspect changes in t1, I need ...
- 4,531
1
vote
1
answer
1k
views
Nested push in Mongoose
I have a user schema like that:
name: String,
nick: String,
email: String,
password: String,
bio: String,
color: String,
followers: [String],
followings: [String],
posts: [{
author: {
...
- 11
1
vote
2
answers
306
views
MySQL: Updating large data set including time aggregation
I am having a large MySQL (5.7) table with millions of rows (contains data for each second). Based on these values calculations should be performed, in some cases for large data ranges. Therefore, I ...
- 13
0
votes
1
answer
30
views
need help in the update query
table name:- **t_ci**
s39 column_name
176
i want to have my table like this
s39 column_name
176 176
But this query works
UPDATE t_ci SET s39=s345 WHERE blueprint_id=1234;
But I ...
- 97
0
votes
3
answers
9k
views
How to update one column with the value of another if one of their columns match
I've found a lot of similar questions, but not exactly what I need. I want to make a SQL command to find all duplicate email rows and update the notes of them with 'do not email' if it is written in ...
- 189
0
votes
1
answer
360
views
How to update a Postgres row atomically, with a computed value that depends on the new values
I have the following table in Postgres:
CREATE TABLE avg (
user_id INT,
numer DOUBLE PRECISION,
denom DOUBLE PRECISION,
frac DOUBLE PRECISION
);
I want to accomplish the following, ...
- 141
0
votes
1
answer
266
views
How to add all combinations of two pairs of key-value to a table?
I have a database of items, as each item has a set of key-value pairs such
place => london
field => engineering
since I frequently need to find items having the same two key-value sets. I ...
- 4,531
0
votes
1
answer
2k
views
How to update a column whose value is being calculated from the values exists in other columns of same table in MySql?
I've a table User_Engagement having columns :-
I was trying to implement this below formula on my update Query to calculate TotalInteraction of all columns in a single query:-
totalInteraction = ...
- 141
0
votes
1
answer
681
views
Subtract multiple quantities in table2 from table1
How can I subtract the quantities in table2 from table1?
I'm using SQL Server 2014.
Table1
P_ID name quantity
1 carrot 100
2 eggplant 100
3 Avocado 100
4 ...
0
votes
0
answers
60
views
Keeping a table up to date that has been created using a DB Query
I have created a new table view for customers, so that when they log in, they only see the information that they need to see...
I used the Query:
CREATE TABLE IF NOT EXISTS Customer( SELECT ColumnA, ...
- 177
0
votes
0
answers
99
views
Is there a purpose for UPDATE ... WHERE but with same values?
I am translating some code from PHP to Python, and I came upon this interesting snippet:
$sql11 = "UPDATE tbl_pas SET api_id_pas='" . $api['kd_pas'] .
"' WHERE api_id_pas='&...
- 187
0
votes
1
answer
1k
views
update datatype field int to decimal128 in mongo db in nested json structure
Hi have tried to updated nested field datatype
db.test.find().forEach(function(data){
data.age = NumberDecimal(data.age);
data.phone.$.mob = NumberDecimal(data.phone.$.mob);
db.test.updateOne({_id:...
- 3
0
votes
0
answers
74
views
Fill in Ending Balance from previous months balance
I need to fill in the Ending Balance of each Year / Period where it is 0 (zero) but a previous period for that year and company has an ending balance - basically carrying forward the ending balance ...
0
votes
1
answer
163
views
Update Mysql records using Same table but Based on same table record
I have table in which i want to update records based on it's 'wishlist_id' value.
Sample format of table records.
Expected table record after update.
So can you help to update this record using ...
- 135
0
votes
0
answers
122
views
Match and update records in a table
I have two tables
table1:
| id | A | B | C | date1 | date2 |
|-----|------|-----|------|--------------|--------------|
| 1 | 100 | 100 | 100 | 10-Jan-2019 | 11-Jan-2019 ...
0
votes
2
answers
80
views
Update values from one table to another where key values are not unique
I've been trying to figure this out for hours and can't seem to get it. If it can be done, any help would be greatly appreciated! I hope I'm clear with what I'm asking :-)
Consider the following:
...
- 3
0
votes
1
answer
2k
views
How to handle possible NULL in a Trigger Updating a table without a Primary Key
I'm using Postgres Triggers to keep two tables in sync for a short term migration. However, I have one join table that doesn't have a primary key, but two columns that function as a composite primary ...
- 101
-3
votes
1
answer
2k
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 ...
- 97