All Questions
31 questions
2
votes
2
answers
556
views
How to update a foreign key with random values from the referenced table?
In order to generate test data for a Postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'.
How to do it?
I ...
4
votes
1
answer
461
views
Concurrent update on two different tables fail
I'm using PostgreSQL with the following database schema:
CREATE TABLE plans (
slug VARCHAR(500) PRIMARY KEY
);
CREATE TABLE users (
id VARCHAR(16) PRIMARY KEY,
org_id VARCHAR(16) NOT NULL
...
- 143
3
votes
2
answers
13k
views
Update with subquery referencing the main table
I'm trying to understand how to filter my subquery in context to the main query. Ultimately I'm trying to get the MAX value from the latest record BEFORE the date of the current record. Here is where ...
- 31
0
votes
2
answers
425
views
Store value of subquery in update for later use
Trying to calculate % change of price. Query below works, but was hoping there was a way that I didn't have to call the same subquery first? I don't think WITH works here unless I'm mistaken.
update ...
- 121
0
votes
2
answers
90
views
How to UPDATE faster with GROUP BY?
I have a typical article and tag_map tables,
CREATE TABLE `articles` (
`ID` int(11) unsigned NOT NULL,
`Tags` tinyint(3) unsigned DEFAULT NULL,
`Title` varchar(512) DEFAULT NULL,
PRIMARY KEY (`...
- 4,531
4
votes
1
answer
6k
views
Unexpected unique constraint violation during update
In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records ...
- 153
0
votes
1
answer
2k
views
Will subquery (using IN) run multiple times for update statement (SQL Server 2017)
Where an update statement is updating the value returned by a subquery - will the subquery be run for each row?
UPDATE a
SET DM = 1
FROM TableA a
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA ...
- 3
0
votes
1
answer
41
views
Need help with correlated subquery
I have two PostgreSQL tables that each contain a column called date_of_incident.
They each also have a column called incident_id and this is how the tables are related.
I want to update all rows of ...
- 3
1
vote
1
answer
474
views
Update rows in table with id from row before in PostgreSQL
Say I have a table like this:
| id | grp | time_added | previous_id |
|----|-------|------------|-------------|
| 1 | 1 | 5 | null |
| 2 | 1 | 8 | null |
| ...
- 113
0
votes
1
answer
756
views
How to optimize UPDATE with a nested SELECT subquery?
I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do:
In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
1
vote
1
answer
516
views
Update "oldest" row
I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n).
I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
- 223
2
votes
1
answer
3k
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 ...
- 47
1
vote
1
answer
510
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 ...
- 13
1
vote
0
answers
1k
views
Update Quantity Available using Quantity from another table
You will demonstrate an update that includes two tables and uses a subquery.
Make sure to prove that your update executed correctly by showing data in the tables before and after the update.
...
0
votes
1
answer
2k
views
MySQL update based on main table data with cross table subquery
I have the following SELECT statement which I have simplified here. It actually takes 0.6s to run as it does some calculations based on two tables (table1 & table2).
SELECT
table1.id,table1....
- 113
0
votes
1
answer
2k
views
Why MySQL performs full table scan if subquery is used in update statement
I have a test table:
create table t1 (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`description` varchar(120) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB;
...
- 3
0
votes
1
answer
6k
views
mysql update based on select result
Here are two SQL statements. One is an update statement and the other is a select statement. I want to combine them.
Update
update customer_entity
set website_id = 4
where website_id = 3
Select
...
- 109
0
votes
3
answers
976
views
Updating rows from other rows in a huge table
I have a huge table with items
item-id | name | type | extra
Many of them are "old versions" of type 0.
I have to update the extra field in all new items (same item-id, but type > 0) with the extra ...
- 172
16
votes
3
answers
2k
views
Slow update on large table with subquery
With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4.
UPDATE [SourceTable]
SET
Bad_Count=
...
- 161
1
vote
0
answers
626
views
Update row value with a sub-select join
I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table.
The query looks like this (...
- 123
1
vote
2
answers
9k
views
How to access row value from UPDATE sub query function?
I would need to use UPDATE like this:
update offer set (bank, bank_id) = (cost, gid)
from (
select cost, osm_points.gid from pgr_kdijkstraCost(
'SELECT gid as id, source, target, walk_cost as cost ...
- 11
3
votes
1
answer
3k
views
MySQL Update a table based on the last data from another table while grouping with conditions
I need a query to UPDATE a series of fields based on another table
Table lp_plates_backup
| uid | brand | model |
| 1 | | |
| a | Old | Error |
|...
- 45
10
votes
1
answer
413
views
Why is my SQL Server query behaving differently on UPDATE than on SELECT?
I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great:
DECLARE @RunDetailID INT = ...
2
votes
1
answer
958
views
Very slow update with subquery (different tables)
I run this query to update "domains" table based on "log" table:
UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
(SELECT log.mail as domain_name FROM log WHERE
`time` >= ...
- 21
2
votes
1
answer
882
views
Update only the oldest entry in a table with foreign key criteria
I'm working on a forum website.
What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts".
"Threads"
"Posts"
I'm not ...
- 23
3
votes
1
answer
28k
views
UPDATE a column by COUNT of another table
It might be a naive question, but what is the difference of these two queries and which is preferred?
UPDATE table1,
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B
SET table1.Freq = B....
- 4,531
2
votes
1
answer
3k
views
How to UPDATE a column by COUNT of FK from another table?
How to extend this query for the entire table, to update all ids?
UPDATE table1 SET number=(
SELECT COUNT(*) FROM table2 where id=1
) WHERE id=1
id is the PRIMARY KEY in table1 and FK in table2.
...
- 4,531
2
votes
1
answer
23k
views
Mysql: update query with subquery
Hello i need to perform the following query
Query
update Taxonomy
set sourceId = (
select id from TaxonomyMapping a where a.oldId =
(
select cm.en_ID
from TaxonomyMapping ta
...
- 23
3
votes
1
answer
6k
views
Oracle 10g: Optimizing an update query that uses a subquery
I have the following query within a stored procedure, and I've been trying to improve its performance to no avail.
UPDATE TBL_REPORTING REP
SET (REP.ISSUER_ID, REP.ISSUER_NAME
) = ( ...
- 31
0
votes
1
answer
1k
views
How to update a table by count from another table?
I have two tables as
CREATE TABLE country_statistics
(
id int(11) NOT NULL AUTO_INCREMENT
us int(11),
uk int(11),
ca int(11),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT
CREATE TABLE entries
(
id int(11)...
- 4,531
1
vote
1
answer
497
views
How to re-arrange rows to best fit within groups?
I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order id num_items cat order_in_cat cumulative_sum folder
1 1 ...
- 4,531