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
1
vote
1answer
64 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 ...
1
vote
1answer
1k 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 ...
4
votes
2answers
314 views

Help with tricky update statement

I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier. I have two table variables: DECLARE @...
5
votes
2answers
19k views

Adding a sequence number to existing rows

I have a table with an OrderSequence column. The schema looks roughly like this: id Name OrderSequence I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the ...
13
votes
3answers
37k views

Update a json element in json datatype

I can't figure how can I update an element in a PostgreSQL 9.3 datatype. My example: CREATE TABLE "user" ( id uuid NOT NULL, password character varying(255), profiles json, gender integer ...
2
votes
1answer
51 views

Regularly updating table with huge amount of rows

I have a table containing (in addition to some metadata) a score and a boolean flag: CREATE TABLE scores ( score_id SERIAL NOT NULL CONSTRAINT score_pkey PRIMARY KEY, user_id ...
-1
votes
1answer
75 views

Improving performance of a query update that uses md5

I need to optimize this query, or change some configuration, because execution takes too much time. Query with rows as ( update hotmail.clean_us_usdataclean_1 set offers_excluded=case when ...
0
votes
1answer
3k views

Updating column based on conditions from another table

I am updating a column to values based on conditions from another table. I want only the values to be updated for rows where the condition meets. It shouldn't overwrite the rows where the conditions ...
0
votes
3answers
4k 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 ...
0
votes
1answer
56 views

Updating across row and column in SQL

Bit of a strange question. Entries in my table have three relevant columns: drug date_1 date_2 seq_id I am trying to make it such that every drug with the lowest seq_id has a date_2 equal to the ...
0
votes
2answers
84 views

Mark exported rows as exported

I need to write an export module which marks exported rows as exported. We currently run SQL Server Express 2012, but may switch to Oracle, so I need a client-side approach. We are currently using ASP....
4
votes
2answers
10k views

Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement?

I have this table: SELECT * FROM items id | item | position ---|-----------|---------- 1 | USB cable | 0 2 | SD card | 4 3 | Mouse | 2 4 | Keyboard | 0 5 ...
0
votes
1answer
19 views

Update record column with id found in the table of an associated model

Given three tables: A users table with id's: ╔════╗ ║ id ║ ╠════╣ ║ 1 ║ ╟────╢ ║ 2 ║ ╟────╢ ║ 3 ║ ╚════╝ A purposes table with user_id's ╔════╤══════════╗ ║ id │ user_id ║ ╠════╪══════════╣ ║ ...
4
votes
1answer
101 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, `...
1
vote
1answer
104 views

Should SQL Server 2000 throw an error if update statement fails within a SP?

I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was ...
-2
votes
2answers
3k views

How to UPDATE along with INSERT INTO SELECT [closed]

I added some entries from table2 to table1 as INSERT INTO table1 (title) SELECT title from table2 WHERE ... how can I UPDATE table2 for the SELECTed or INSERTed entries as UPDATE table2 SET status='...
4
votes
1answer
9k views

column {table_name} of relation {table_name} does not exist SQL state: 42703

I am getting an error while running an update query with table name specified along with column name: UPDATE Temp SET Temp.Id='234',Temp.Name='Test'WHERE Id='245' This is the error: ERROR: column "...
0
votes
1answer
48 views

How to accomplish such insert-or-update strategy?

Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better. I've stumbled upon a business requirement that's kind of hard for me and I was ...
1
vote
2answers
441 views

Concurrent UPDATEs on INNODB tables in MySQL

I need to UPDATE all rows for multiple columns on a large INNODB table. Each UPDATE processes one column separately and takes about 1 hour. I have to do this for 10 columns so it would take 10 h I do ...
43
votes
4answers
271k views

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT ...
1
vote
1answer
63 views

Why is this very simple UPDATE so much slower than the SELECT it contains?

In MariaDB 10.3.9: SELECT COUNT(*) FROM photos vs UPDATE counts SET countValue = (SELECT COUNT(*) FROM photos) WHERE countName = 'photos'; The select takes 1.1s vs the update which takes 2.6s. ...
0
votes
1answer
104 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 ...
0
votes
2answers
127 views

Understanding why a query is slow

I have a new column in SQL Server 2016, on a table of type bit. (I wanted it to be a persisted calculated column but was overruled and asked to make it a bit that is maintained by program code). Its ...
3
votes
2answers
37 views

How can I instantly update a table in a database with the columns values from another database?

I have database_1 and database_2 both on the same server. How can I instantly update the USER table in database_2 when data is inserted into the USER table in database_1? Structure of both tables: ...
1
vote
1answer
3k views

MongoDB update multiple sub documents inside array, each according to a condition

I have a document like this: { wid: 1, items: [ {nid: 1, sent: 900}, {nid: 2, sent: 12}, {nid: 3, sent: 0}, {nid: 4, sent: 1} ] } Now consider I want to ...
0
votes
1answer
3k 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 ...
1
vote
1answer
63 views

Is it harmful to update columns which aren't changed?

Say I have a table with some columns: CREATE TABLE t ( key int PRIMARY KEY, c1 int, c2 text, c3 timestamptz ); I now fetch a row from the table: SELECT * FROM t WHERE id = 180; ...
0
votes
2answers
500 views

UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT'

I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table. To me, the query is ...
2
votes
1answer
672 views

Temporarily disable indexes before updating whole table

For testing purposes I periodically restore production backup into a new database (AWS RDS PostgreSQL instance) with pg_restore. In the new database I run SQL queries that anonymize sensitive data. ...
0
votes
1answer
123 views

Update only if entry does not exist

I have to update many records. I don't want to duplicate the entries. The table concatenates the value to the end. So in theory, I could add the value twice. I want to put a check to make sure the ...
1
vote
2answers
2k views

Update single field in a document (document.update($set)?)

Can't seem to figure out how to update a single field. Whatever I try, to update one field (picture: "url") the rest of the fields are being removed and set to null. I use the following command now: ...
0
votes
1answer
31 views

What is the best solution to prevent picking the same value (by multiple requests)?

Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick ...
2
votes
1answer
6k views

Delete an object from within an array using JSONB in PostgreSQL [closed]

I am attempting to remove an element from my database. The element I want to remove is within a JSON object called playerContainer which contains an array named players. This all sits within a table ...
0
votes
2answers
131 views

creating a trigger after update exclude a column?

I want to create a trigger that set a column to to_sync true if the other columns has been changed. CREATE TRIGGER [dbo].[product_trig] ON dbo.product AFTER UPDATE SET NOCOUNT ON; IF NOT ...
1
vote
1answer
55 views

What is the function that determines the maximum value in a series?

Say I have a table with columns C and D (among others but these are the relevant ones) with the date type. I am instructed to update the values in C to be 90 days before its corresponding value in D, ...
1
vote
1answer
61 views

Why does this tsvector update use so much space?

This is my table structure: Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+---------------...
3
votes
2answers
1k views

Remove substring from one column and put it on other column

I have a table like this: CREATE TABLE `mytable` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `column1` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `column2` varchar(255) COLLATE ...
2
votes
3answers
302 views

Large update and delete based on another table

I have a very active PostgreSQL 10.4 DB, dealt with through SQLAlchemy, so SQL is not my forte. Among its tables, there are master with a primary key id and some nonindexed string column value, and ...
0
votes
2answers
49 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
votes
1answer
693 views

Order of Execution of Set clause in Update Query (Postgres)

I recently came across this strange behaviour in Postgres. I had a table like the following one: sasdb=# \d emp_manager_rel Table "db3004db.emp_manager_rel" Column | Type | ...
1
vote
1answer
210 views

Best way to prevent deadlocks and server locking for single row Insert/Update transaction?

I have Update transaction that will run for single row at the time. However, my application is multi thread and there might be more than one user at the time trying to access same table. While doing ...
11
votes
2answers
88k views

Updating multiple rows with different values in one query

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand. For instance, two updates ...
1
vote
2answers
565 views

Unable to update MySQL table from linked server on SQL Server 2014

I'm using SQL Server 2014, with a linked server to a MySQL database on AWS. I need to update rows on that linked server, using data that exists on another linked server. And if that wasn't complicated ...
3
votes
0answers
69 views

Where is Newid(), Rand(), and Sysutcdatetime() new value per query vs. row behavior documented? [duplicate]

Within a single query, all current time functions like sysutcdatetime() return a single point in time that is re-used throughout the query for each instance (e.g. of sysutcdatetime()) no matter how ...
1
vote
2answers
4k views

How to run update query on multiple table via joins simultaneously on postgres?

In MySQL, I can do a query like this UPDATE prd_sectionshapename se inner join prd_shape s ON s.id = se.shape_id SET se.company_shape_name = 'ABC', s.name_en = 'Another name' ...
1
vote
2answers
376 views

Update selected data from same table column as currency

I am trying to update selected data from a table column and format this data as currency. My table column holds strings that have both mixed whole numbers and already formatted data. This was due to ...
-3
votes
1answer
2k views

Updating a string field by concatenating 2 int columns

I have 2 columns, an int and a tiny int, and I want to update another column, a varchar one, with the value column1 + '-' + column2. Something like this pseudo code: UPDATE COLUMN3 varchar = CONCAT (...
-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
vote
1answer
160 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
40 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 ...