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

1
vote
1answer
673 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 ) = ( ...
1
vote
0answers
200 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
1answer
88 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
2answers
5k views

Use CASE to select columns in UPDATE query?

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so: SELECT CASE WHEN val = 0 THEN column_x WHEN val = 1 THEN column_y ELSE 0 END AS ...
0
votes
2answers
68 views

For SQL Server, What is the proper way to update a table?

I don't have a mentor to ask so I am seeking direction from the DBA community. I am in charge of a SQL SERVER 2008 R2 and SQL Azure databases that are the resources for a website and an ERP system for ...
0
votes
1answer
73 views

Performance on updating row

When updating in SQL Sever, it is my understanding that the entire row is deleted and then recreated (which is why you get a new timestamp). So then, if you update 1 field or 500 fields, does the ...
0
votes
2answers
1k views

Update year alone in date (Oracle 11g)

When I checked one of my client's database, I found some mistakes which will make problems. The problem is that the date is showing as 01-01-01 when I try to display it in the dd-MM-yyyy format the ...
0
votes
1answer
158 views

UPDATE statment with and without SELECT keyword

I have been doing some testing on removing the SELECT keyword from this update: Update #tmpDriverTime Set WorkDay2 = (Select IsNull(Sum(PyblHrs),0) From spr_DriverTimeEntry Where ...
0
votes
2answers
1k views

how to set a row's value from a certain row's value?

I have a MySQL table named "activities" with fields id(int), tstamp(timestamp), amount(decimal) balance(decimal). Decimal fields hold money values. id tstamp amount balance ...
0
votes
1answer
1k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
0
votes
1answer
143 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
0
votes
1answer
316 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 ...
0
votes
1answer
521 views

Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2?

We have an test database configured with our web application. Due to internal web application error the entries were get updated in the database. Is it there any way to recover the updated records? ...
0
votes
1answer
2k views

update column based on the sort order of another query

I'm trying to add arbitrarily ordered records to a database so that they can be sorted by the ui (or when I query the database ). My problem is I already have the list, and I need to add a default ...
0
votes
2answers
52 views

MySQL ON DUPLICATE KEY UPDATE performance issue

We have a table with over 200k rows. Every row belongs to a category, there are almost 50k rows per category. What is the best way to update all rows of that category? We are using a script like ...
0
votes
1answer
89 views

UPDATE Query replacing string primary key taking days to resolve

I'm working with a DB (not designed by me) that had the primary key set to a 47 character length string, which is causing problems due to the fact that the table has over 20 million rows, and this ...
0
votes
1answer
49 views

Postgresql condition on subquery results

This is a new question based on the solution to my previous question I have the following tables: users int (pk) user_id date last_visit int (fk) local_site sites int (pk) site_id site_stats ...
0
votes
1answer
86 views

Issues with 'plus symbol' output in PSQL on update query

Simple but frustrating issue... I'm trying to do an update command to correct a misspelling with this field: grouper=> select * from master_list WHERE group ~ 'JustAdopted'; group | ...
0
votes
1answer
120 views

How to UPDATE MAX and MIN of a GROUP BY?

I have a SELECT query of a table with GROUP BY id col1 category 1 some 1 2 x 1 3 another 1 4 x 2 // should be removed 5 some 2 6 x ...
0
votes
1answer
736 views

INSERT / UPDATE to merge data, when empty data is an empty string

Consider a table of some family members: mysql> SELECT * FROM some_table; +--------+--------+--------+ | f_name | l_name | work | +--------+--------+--------+ | Meirav | Cohen | school | | ...
0
votes
1answer
92 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
0
votes
1answer
419 views

Mysql update table using ubuntu terminal

I have a CSV file that has 2 columns Name Email I have a table in my database that has the same columns + some extra columns. Now I would like to update my db table using that csv file. I mean ...
0
votes
1answer
118 views

MySQL Replace function throwing error

I am trying to update ^G (Ctrl G) in a table, below is scenario- CREATE TABLET TEST(COLA INT,DESCRIPTION VARCHAR(20)); I have crtl G in my data for description field, so to replace it with a \,I am ...
0
votes
1answer
143 views

String replace using concatenated strings from various columns

I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order. The specification says: ...
0
votes
1answer
2k views

Error “#1118 - row size too large” on the first row of the table only

I ran into a problem using a mySQL database. I have some columns as type text but when I try to enter data into the first row I get the error code "#1118 - Row size too large. The maximum row size for ...
0
votes
1answer
124 views

MySQL-5.1 is not updating some rows with a simple UPDATE query [closed]

I'm trying to update a column in a table to be the same as the contents in the column of another table as part of a de-normalization process for a data warehouse project. The data is confidential, so ...
0
votes
1answer
2k views

How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?

I have to create a xls datafeed for a website and I would like to know which tables are getting affected when I do a manual entry from CMS. If i have installed fresh database and I'm doing first ...
0
votes
1answer
81 views

Comparing two tables for a UUID change and fix it

I have two tables which I'm trying to reconcile the differences of in postgresql. Table A is old and needs updating. Table B is an updated, schema identical version of Table A which I have the data ...
0
votes
1answer
123 views

MySQL UPDATE statement using a table created on the fly

I want to update a table, say Table 1 using a table that I create on the fly (saytemp). To achieve this I have the following query: UPDATE Table1 SET COUNT = temp.ctemp FROM ( SELECT A, SUM(UNO) AS ...
0
votes
2answers
1k views

How to do something like UPDATE DELAYED in MySQL

I have an averages table that should keep track of an average value over time. I don't want to have a row for each value, just a single row that continuously updates the average. What I've come up ...
0
votes
0answers
37 views

Update table from different table

UPDATE RBI.RBI_OV_F_DETAIL A SET (BRANCH_ID, BRANCH_NUMBER) = ( SELECT PRICE_BRANCH_ID, PRICE_BRANCH FROM RSE.RSE_SO_F_ORDERS B WHERE A.SOURCE_ID = ...
0
votes
1answer
30 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 ...
0
votes
0answers
30 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
40 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
1answer
26 views

Trigger on a Schema

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one. --Database level trigger. ...
0
votes
1answer
12 views

How to minimize the writes needed to update a counter field multiple times

I need to minimize the number of queries that are executed just for updating a counter field. One of my example counter fields is raised by 1 everytime a page is visited, I was wondering what could ...
0
votes
1answer
31 views

MySQL Update Multiple Rows in Large Table

I have a large InnoDB MySQL database table. I would like to run this query against the database table: Update myBigTable Set someColumn=x WHERE userId = 7 OR userId = 88 OR userId = 22 OR userId = ...
0
votes
2answers
25 views

How can I maintain old information after an update?

I am having an issue figuring out a solution to not lose old information. For example, customers can place many orders. Let's a customer, Mary, makes her 1st order in 2013. Fast-forward to 2014, ...
0
votes
0answers
19 views

How do I stop views (large data sets) from freezing my database

I am working with tables that contain upwards of 200,000 rows. My boss wants to use views that refer to other views in order to have things be more automated. The problem is that the MySQL server ...
0
votes
1answer
48 views

Update previous value field

I inherited a database that has multiple tables that contain some form of an object ID, a date, and a value. The metrics depend on comparing the current value on any given date to the previous value ...
0
votes
1answer
22 views

Mongo DB - reading during update

Is it possible in a three-member-replica set (1 primary, 2 secondaries) to update records on the primary, or even delete several records and insert the updated data, and still execute read-ops on the ...
0
votes
1answer
83 views

phpmyadmin not working after mysql update

i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal. But now there is another ...
0
votes
0answers
26 views

Oracle after insert trigger vs update performance in plsql block

I want to know whether an after insert trigger is better than an insertion statement followed by update statement in plsql block.
0
votes
1answer
76 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` >= ...
0
votes
1answer
38 views

How to update a column in a table with data from another, unrelated tables columns data jus in order it selected?

There are two tables that unrelated. I want to update a field in first table with data that selected from second table in some order. There is no relations between tables, fields and data. I have just ...
0
votes
0answers
32 views

Access 2013 compare changes made to table

I am currently working on an Access 2013 database and trying to find the best solution to a problem I am having creating it (I am fairly new to databases so bear with me). Situation: Data is imported ...
0
votes
1answer
101 views

update variable from multiple tables in one row

I have two databases, and I need to UPDATE variable 'birthday' from one row to another checking 'name' (from db1 to db2). The problem is, that 'name' variable in second db is located in another table ...
0
votes
1answer
127 views

UPDATE CONCAT without duplicates

I would like to concatenate multiple values ​​and remove duplicates (case-insensitive) in the same field with SQL only. Without using a unique key or stored procedure. Ex. ...
0
votes
0answers
74 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
61 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 ...