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.
811
questions
0
votes
3answers
35 views
update one column based on query containing another (joined) table
Consider the following select query:
SELECT s.id, s.event, s.column, e.oldcolumn
FROM section as s
INNER JOIN event as e
ON s.event = e.id
WHERE s.id = 1
Now I wish to update the section table so ...
0
votes
0answers
11 views
Why Mysql only updating single row when the where condition have multiple rows?
My this query always update only single row, while there are multiple rows with gender 'M':
UPDATE users
SET
hire_date = DATE((SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2014-1-1') + FLOOR(RAND() * (...
0
votes
1answer
14 views
Update large number of row in MySQL table
I'm using relational database(MySQL 5.7). On this database i have a table called customer_transaction. On this table i have 4 columns: id, customer_id, type, amount
|id|customer_id |type |amount|
...
1
vote
1answer
14 views
Create a key-preserved view across Oracle tables with descending indexes
I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and:
Do not have primary keys - only a ...
0
votes
1answer
91 views
how mongo db does locking for updates/inserts?
I have many data for each user that with write heavy and many for each separate users that there is not any relation between user's data; so there is two options for me to store them:
1. keep all user'...
0
votes
1answer
20 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 ...
0
votes
0answers
11 views
How to know user intentions when updating a record in a table [closed]
I am using Entity Framework to manage an SQLite db.
The user can create an entity object (with the required primary key for identification) and pass it to me so I can update it in the db. My problem ...
0
votes
1answer
405 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 ...
2
votes
1answer
976 views
Updating a table which contains an Image datatype column
I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF.
I have a requirement to update one ...
1
vote
1answer
1k views
SQL Server Trigger does not update all table records
This is my first question so please go easy on me. We are using Access 2013 as a front-end and SQL Server 2012 with MS SQL Server Management Studio 11.0.2100.60 backend with ODBC connections.
I am ...
0
votes
1answer
26 views
UPDATE SET values ignoring set ordering
I'm trying to update 2 rows at once, I want to:
1) Set coupon.start (datetime) to the original account.vipExpires (datetime)
2) Add coupon.days (int) to account.vipExpires (datetime)
3) Set coupon....
0
votes
2answers
15k views
How to update a column with values from the same newly inserted row in a trigger?
Using SQL Server 2014, I have this:
Table1
Tab1_id int identity(1,1) primary key
Tab1_val1 smallint
Tab1_val2 smallint
Tab1_valInfo varchar(10) -- not yet implemented, will be used ...
2
votes
1answer
794 views
Update query could not be bound
I'm trying to do a simple update query and I'm getting this error:
The multi part identifier "customers.customer_name" could not be bound
Query:
Update Suppliers
Set City = (select s.city
...
0
votes
2answers
175 views
How To Ignore SQL Update Notification
I have a very weird situation.
One of our SQL Servers has a software update notification pending for Microsoft SQL Server 2008 R2 Service Pack 1 (KB2528583). However, this server is already at SP2. ...
-1
votes
2answers
2k views
Using an Aggregate In Merge Update Statement
I am attempting to run a COUNT() statement in an update statement. However my syntax presents this error
Msg 157, Level 15, State 1, Line 6<br>
An aggregate may not appear in the set list of ...
0
votes
2answers
49 views
Update Grouped records
I have the following query that returns duplicate values:
SELECT
[t].[Field1], [t].[Field2], COUNT([t].[Field2])
FROM [dbo].[Table1] AS [t]
WHERE [t].[Field2] <> ''
GROUP BY [t].[...
1
vote
1answer
73 views
Index that mimics full-text-index
I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a .sdf file and Sql Server Compact ...
2
votes
1answer
51 views
Better way to update table with value from another table
I'm trying to rewrite the following query to make it more efficient:
update c set c.cartype = (select Id from carTypes where carType = 'Coupe')
from cars c
inner join doorcount dc
on c.Id = dc....
0
votes
1answer
48 views
How do I drop the TIME portion from DATEADD?
I am trying to create some test data where each Employee has a random birthdate. I accomplished the randomizing part but it did not return result in the format that I want.. NOTE: the DateOfBirth ...
2
votes
1answer
3k views
How to update primary key table based on foreign key with ON DUPLICATE KEY UPDATE
I have a users table with autoincrement id as primary key, and unique key on email. The email and name fields are from facebook.
+----+-------+------+
| id | email | name |
+----+-------+------+
And ...
33
votes
4answers
24k views
Does updating a row with the same value actually update the row?
I have a performance-related question. Let's say I have a user with first name Michael. Take the following query:
UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123
Will the query actually ...
0
votes
1answer
228 views
Affected rows after update with OpenQuery
Is there a way to get the affected row count, when updating through an connection server?
I'm using MSSQL connected to MySQL with ODBC.
UPDATE OPENQUERY (MyConnection, 'UPDATE recordid, status FROM ...
0
votes
2answers
23 views
slow UPDATE of rows by COUNTing from another table
I try to update a table by counting rows from another table as
UPDATE t1 SET c1 = (
SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
)
strangely, this query stuck at Sending data state ...
-2
votes
1answer
37 views
Am not able to execute the update stmt using Multiple Joins..SQL DEVELOPER [closed]
UPDATE artikel A
SET A.art_haccp = (SELECT DISTINCT a.art_haccp
FROM artikel A,
warengruppe W,
sparte B,
...
0
votes
1answer
14 views
Bulk update an address column where the address was duplicated with a delimiter separating the duplicate?
Sorry if my title doesn't make much sense, I wasn't sure how to word it though.
Basically I imported a file containing 500+ of stores and information about them. For some reason the [Address] column ...
1
vote
1answer
43 views
update matching column value in anywhere in database sqlserver
I have assetid column in multiple table in my Database and Asset(main) table
I want to update all occurences of tables in all occurences of inserts in database assetid column where value has 1020 to ...
0
votes
1answer
10 views
What is the safe way to update production data if data found inconsistent
I am in a small company and require to fix a bunch of data in production which are inconsistent. I have written the script to handle the fix.
I understand that writing sql to fix data is way more ...
0
votes
1answer
251 views
Correcting and Detecting overlapped dates with CTE
I have a CTE that marks (as bits) overlapped records based on certain criteria. After filtering, I need to update these records to "correct" them and make them sequential, but I'm getting a 4104 on ...
12
votes
3answers
59k views
UPDATE table based on the same table
I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same ...
0
votes
0answers
11 views
Psycopg2 using value SET before assign to other column
I have a table with columns as below: (id, col1, col2, col3, col4). I need to insert new row to this table, with given data is a dict: data = {'id': 1, 'col1': 1, 'col2':1, 'col3':1, 'col4': 1} for ...
1
vote
2answers
67 views
How to combine two columns into one?
I have a table in PostgreSQL 11 with two varchar columns like this:
A | B
---+---
m | n
x | y
Now I want to add a column C which should contain the contents of A and B (concat(A, B)):
A | B ...
8
votes
1answer
14k views
How to remove known elements from a JSON[] array in PostgreSQL?
I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
2
votes
1answer
94 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. ...
1
vote
2answers
248 views
Replacing MYSQL table with data from another table - no downtime
I have two tables with identical structures:
Table1 and Table2
id INT PRIMARY KEY,
a TINYINT,
b TINYINT,
c TINYINT
Table1 is actively being used by queries from other sessions; Table2 is not ...
2
votes
1answer
76 views
Overwrite event - data from SQL Server to MySQL
In MySQL is it possible to execute an update query with values selected from external SQL Server database?
I'd like to execute every night a timed query, which overwrites some data in MySQL with data ...
0
votes
0answers
27 views
MySql uncomplated Upgrade Error
I faced showed bellow error every apt upgrade. An mysql server frequently down. I try backup my database with mysqlpump, this backup not contain tables values. Only table construct. How can I fix this ...
0
votes
0answers
18 views
Problems Add/Update Big Data on PostgresSQL
I want to add large amounts of data to the table.
Before adding, I check whether the data exists in the table or not.
I am dealing with the following:
Example:
Table users
id | name | address
.....
0
votes
0answers
26 views
UPDATE records in one table, several times the message (1 line affected) and then the actual message of the update
I have discovered the following oddity:
When I execute a SELECT statement, I get my desired 753 records displayed.
But when I convert this SQL to an UPDATE and I execute an SQL with the following ...
1
vote
2answers
6k 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
3answers
275 views
MySQL - Deduct from two columns with a remainder
I have the following table:
Id, balance, otherBalance
=============================
1, 100, 50
I would like to create an update query that receives a parameter 'amount' and does the ...
0
votes
1answer
138 views
I Want To insert data in transaction table and at the same time update customer table in codeigniter
(codeigniter) I Want To insert data in transaction table and at the same time update customer table .
I want to Add value of ChangeDue in transation table To value now_debt in Customer Table
...
2
votes
1answer
80 views
UPDATE on column referenced in sub-query
We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following:
update CE_WORKSET_READ_ENTRY workset
set workSet.MARK = 0,
...
1
vote
1answer
38 views
update using sum
I am trying to update the totalNumber column of the AccountDetails table to be the SUM() of the totalNumber column of the Trans table, where the accountID and the assetID columns match.
The is the ...
1
vote
1answer
79 views
Translate SQL Server update statement to MySQL
I have a development environment with MySQL and a production environment with SQL Server.
This update statement works with SQL Server:
UPDATE THIS
SET <list_of_column_assignments>
FROM <...
1
vote
2answers
396 views
Mysql innodb table dead lock while batch update in multiple thread
I've two tables of innoDB type and I'm updating one table using batch statement from 10 thread (one batch statement per thread) but some time I'm getting dead lock.
java.sql.BatchUpdateException: ...
-1
votes
1answer
352 views
Update table with value from another table
I have two tables:
AudiTable
Id Audi_cod status marke1
1 011 mid Null
2 022 mid Null
3 033 mid Null
RenoTable
Id ...
2
votes
2answers
130 views
Can I use OUTPUT from an UPDATE in an INSERT?
I want to add a record to my app's "SystemSettings" table and set the PK value using the value from an UPDATE. The PK value comes from the "TS_LASTIDS" table which contains the maximum PK value for ...
0
votes
2answers
91 views
Update a table with data from another table
I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. ...
0
votes
1answer
19 views
how can I update some table rows using data from existing rows?
I have created a table where it stores some multilingual store data with this structure
create table store_locator_store_lang
(
id_store int(11) unsigned not null,
lang_id int not null,
...
5
votes
2answers
427 views
Why is there an aggregate in this UPDATE plan?
Given this
declare @Data table (id int, fact char(1));
declare @Summary table (id int, collected varchar(99));
insert @Data(id, fact)
values
(1, 'a'),
(1, 'b'),
(2, 'c'),
(2, 'd'),
...
