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.

learn more… | top users | synonyms

2
votes
1answer
107 views

Updating large table worked before, now taking hours

BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not ...
2
votes
1answer
39 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
1answer
47 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is ...
1
vote
1answer
43 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 ...
1
vote
1answer
470 views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...
1
vote
1answer
215 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
1
vote
1answer
195 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` >= ...
1
vote
1answer
151 views

scenario to update or insert big amount of data

I try to find the best scenario to update or insert 1 million contacts in my postgres database. My actual scenario is: read contacts 1000 per 1000 and try to insert multiple rows with one request ...
0
votes
1answer
30 views

Update only some records

I search an update-command, which update me only some records (more than one row) in a table. The update Information comes from an other table. Unfortunately it updates all 3500 records instead of ...
0
votes
1answer
52 views

Tracking the update or changes in mysql table

I need to stream the changes to elasticsearch to keep in sync with my database(mysql). What will be best way to do this ? I have thought for below approaches Using trigger( but my table will have ...
3
votes
0answers
90 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 = ...
3
votes
0answers
160 views

Update all rows

I want to know the most efficient manner to update every row in an extremely large Oracle table for a single column. For example: update mytable set mycolumn=null; or: update mytable set ...
3
votes
0answers
105 views

Unable to Update on SQL Server 2008 R2

This is a bit strange so bear with me please. After installing a new SQL Server 2008 R2 on a new server (Windows 2012 Standard) just like the production one, I tried to connect my web aplication ...
2
votes
0answers
34 views

How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions: CREATE TABLE products ( id bigint NOT NULL, title character varying(75), description text, manufacturer_id bigint, created_at ...
2
votes
0answers
30 views

How to order by fields of joined table while selecting for update?

For selecting some rows of table Fwt_Chipkarte for update I use the following SQL statement (IBM DB2): SELECT o.Archiviert, o.Id, o.IdComm, o.Kartennummer, o.Konto, o.LetzteAenderung, ...
2
votes
0answers
42 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
2
votes
0answers
89 views

How to optimize update using join in postgresql

I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems ...
2
votes
0answers
303 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
1
vote
0answers
46 views

How to use the value from record like as varchar?

I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; ...
1
vote
0answers
119 views

Update on a small table times out

We use Microsoft SQL Server Express Edition with Advanced Services 2008 R2 SP2 We have two locations. Each location has one sql server that serves about 20 client computers. Client computers are ...
0
votes
0answers
18 views

PostgreSQL 9.3: Minimize locking when updating a large table with primary key

I am building a DB solution to handle a fast stream of online events. Each event UPSERTs a row in a location_update table, using its car_id primary key. Since there are very few new car_ids, the ...
0
votes
0answers
42 views

Auto increment form field

I am working with a database, with some forms. What I am trying to do is an auto increment field in my form. For example ID -- every time when I type some new record, this field ID should increment ...
0
votes
0answers
20 views

Updating values with randomly generated values?

I've got an .sqlite3 database here. I want to replace all the values of a set distinct number of columns in the database with randomly generated values (the values should be unique). At the moment ...
0
votes
0answers
28 views

subquery in set clause retrieving different values

I want to update the column "kstplcode" using different values coming from column warehouse except the value "main" with their corresponding delivery numbers. I've tried this code but the set clause ...
0
votes
0answers
23 views

PostgreSQL updates hang when two servers involved

I have an update statement that works fine when only a single server is involved, but never completes when it comes from two servers. I'm connecting with JDBC 9.1-902 to a 9.3 database on CentOS 6.6. ...
0
votes
0answers
25 views

Update MySQL column from column in another table

I have a table, products2013, with column upload_id which I would to contain the value upload_new_name from correlating table uploads (also containing upload_id). I tried putting together the follow ...
0
votes
0answers
38 views

MySQL - Update vs. Replace Into — why is Update non-parallel, and Replace Into allows multi-threads?

In trying to optimize a query that does (Insert ... on duplicate key update) .... someone suggested I do "Replace Into" to optimize performance. I'm not 100% certain the difference other than ... ...
0
votes
0answers
33 views

I have a table from which I can retrieve information but I cannot update or delete information

I can update and delete items via PhpMyAdmin. I changed the owner to mysql. My program is a clone of insert, update, and delete that is functioning for other tables within the database. I receive a ...
0
votes
0answers
41 views

How can I use PostgreSQL's partial sum tree index (if it even exists)? Do any databases allow for use of such an index?

GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ...
0
votes
0answers
14 views

Checkpointing in a deferred update database

Something that I just cannot get my head around. Checkpointing writes all dirty pages from ram memory to disk. For Deferred update systems, data changes recorded in the log file are applied to the ...
0
votes
0answers
35 views

PostgreSQL slow update -> does autovacuum have anything to do with it?

I am quite new to PostgreSQL as I only need it to create a map (using PostGIS). So I don't really know on hot how to optimise my DB... I have a table with a lot of polygons that I'd like to save in a ...
0
votes
0answers
40 views

Oracle SQL Updating Trigger Statement

Hey all so let me see if I can clearly explain what I would like this trigger to exactly do. First of all the trigger will deal with 2 tables: Delivery Table: DeliveryNum |ItemName | Quantity | ...
0
votes
0answers
24 views

Efficient Trigger updating in Postgresql

So I have a big table that often needs specific columns updated for a large number of rows. So right now I have it set up so that there is a 'staging' table that has the update-able columns. Then upon ...
0
votes
0answers
35 views

How to find Deleted and Updated record tables in MYSQL database?

I have scenario like below. I need to find Deleted records and updated records effected tables in mysql. Ex: I update my Name or Email Id in UI that is effected in some tables either one table or ...
0
votes
0answers
63 views

Sleeping queries blocking rebuild index and update statistics job in Always on

At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is ...
0
votes
0answers
40 views

Update on table deletes the record

I'm having a problem in my oracle database, I have a table PAYMENTS which has an ID (PK) and when I want to update a record: UPDATE PAYMENTS SET AMOUNT = 2000 WHERE ID = '1'; The record with ID = ...
0
votes
0answers
41 views

Does storing page views in my page table slow performance?

I am storing page views in a view_countcolumn on my page table. So every time the page gets loaded I increment the view count and update the entry. What affect can this have on the performance of my ...
0
votes
0answers
89 views

What is the best way to execute a MySQL script periodically on Linux

I want to execute some SQL everyday in a Linux server using MySQL to UPDATE some column's values. In order to do that I use the UPDATE ... SET ... WHERE ... statement. I want to know which is the ...
0
votes
0answers
43 views

long mysql insert and update script user variables appear to be lost while running script

I have a mysql database table called 'dash_something' dash_something ------------- date, DATE *this is the date of the summary type, CHAR(10) entity, CHAR(25) number_of_emails_1, INT ...
0
votes
0answers
47 views

Update existing (old) rows vs delete and insert when needed

I'm building a database system that needs to store login tokens for a system that validates if a users login token is valid or outdated. The table is pretty simple UserID int Primary Key LoginToken ...
0
votes
0answers
56 views

Bulk update performance

I have a several process that aggregate some statistics on items (how much was shown, clicked ... around 30 factors). Once in a 5 minutes i flush the aggregate data to postgres (9.1). I have 250K ...
0
votes
0answers
277 views

Updating +150,000 rows using .sql file causes ERROR 2013 Lost connection to MySQL server during query

I am using ubuntu 12.04. I have a .sql file that is around 5 megabytes. When I use the command: mysql -u -p -v database query.sql The server prints out the content of the file (because of --verbose) ...
0
votes
0answers
208 views

How do I perform a batch update on the first n matching rows?

I need to perform a large number of updates at the same time. Each update needs to only affect up to a specific number of rows even though the WHERE clause might match to many more rows. So far, I'm ...
0
votes
0answers
203 views

Multiple update of records in the same table

I have an update query. E.g. UPDATE table SET name = ? WHERE id = ? Now I have all the relevant ids collected. What I am doing is looping over the ids and execute the update query for each one. E.g ...
0
votes
0answers
1k views

SQLite UPDATE two tables from VIEW or TEMPORARY TABLE

Here is the query that doesn't compile just to show how it should look like. But since I can't do Multiple table UPDATE's. This fake query should be able to run the inner UPDATE's for both tables if ...
0
votes
0answers
91 views

Inserting and updating on same table

I am currently working on a database that aims to provide information regarding the amount of time people stay in a company's car-park premises. This is done using a table called "tbl_stay" which has ...
0
votes
0answers
401 views

Alternatives for UPDATE statement Oracle 11g

I'm currently using Oracle 11g and let's say I have a table with the following columns (more or less) Table1 ID varchar(64) Status int(1) Transaction_date date tons of other columns And this ...
0
votes
0answers
159 views

SQL queries are very slow

I have noticed that my queries are getting slower. Some update queries take about 48-50 seconds. Here's how it looks: [root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log # Time: 131113 17:09:48 # ...
0
votes
0answers
193 views

mysql table not updating no errors

i am using mysql 5.2.44 CE revision 9933 i have a table in which i am trying to update a few columns of the rows i am not using queries, just doing it using the edit table UI provided in the ...
0
votes
0answers
109 views

Being careful about additional records being added in a duplicate record merge?

We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've ...