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
2answers
165 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
1
vote
1answer
98 views

Create constraint for stopping mass update to a table

I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.
1
vote
1answer
604 views

UPDATE SET REPLACE() matches but does not change

I've seen another post about this exact thing but it's 1-1/2 years old so I thought I'd make my own...besides, the other post did not help me. What I am trying to do is pretty straight-forward. To ...
1
vote
1answer
3k 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 ...
2
votes
1answer
1k 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 ) = ( ...
0
votes
1answer
169 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. ...
1
vote
1answer
157 views

How to UPDATE a table for the level of parents in a column?

I have a table with internal parents as id parent parent_level 1 NULL 1 2 1 2 3 1 2 4 3 3 where parent has a FK to id. How can I update the table ...
8
votes
3answers
4k views

How to optimization database for heavy I/O from updates (software and hardware)

The situation I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some ...
0
votes
1answer
384 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 ...
1
vote
2answers
185 views

Install SP 3 on SQL Server 2008, where merge replication is running

I have a SQL Server 2008 and I should install Service Pack 3 for it. On my server there are several databases replicated, and mobile devices sync data via merge replication. Can problems occur, if I ...
0
votes
1answer
618 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
2answers
2k 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
127 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 ...
2
votes
2answers
2k views

After update triggers that deal with multi-row updates

Currently working on an database Audit project based on triggers that are fired on update on specific tables. The triggers write the changes into a table; information written are: table name, updated ...
0
votes
1answer
169 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 ...
-1
votes
1answer
56 views

What could I possibly be doing wrong with this update statement

UPDATE pcdbak SET pcdbak.Title = pcd.Title FROM pcd, pcdbak WHERE pcdbak.eCommNum = pcd.eCommNum This was the error message: [Err] 1064 - You have an error in your ...
1
vote
3answers
2k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
5
votes
6answers
1k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
0
votes
1answer
177 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: ...
4
votes
1answer
350 views

Oracle UPDATE followed by SELECT in LinqPad

I'm trying to UPDATE values in an Oracle database table (of salary information) that has no row IDs or a primary key. I'm writing these statements in LinqPad. This syntax should work just fine in ...
-4
votes
1answer
146 views

Update date Column values? any please help? [closed]

I Want to copy and data in same Table and Update date Column values? any please help?
0
votes
1answer
626 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

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 ...
4
votes
1answer
2k 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 ...
-1
votes
3answers
113 views

Add Contents in a Column and make them 0 [closed]

http://sqlfiddle.com/#!3/96f11/3 In the above fiddle, I need the required output in the Fiddle. ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data ...
7
votes
2answers
5k views

Optimizing bulk update performance in PostgreSQL

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = ...
3
votes
1answer
1k views

update statement with self join

I needed to copy information from one row of a table to another. I noticed I could do the following: update address set col1 = a2.col1, col2 = a2.col2, .....etc from address a1, address a2 where ...
2
votes
1answer
174 views

Updating column in underlying table causes massive transaction log growth

I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry ...
0
votes
2answers
7k 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 ...
1
vote
3answers
156 views

How to handle update or insert situations

From time to time my stored procedures looks like create procedure handle_data @fk int ,@value varchar(10) as begin if exists (select * from my_table ...
1
vote
1answer
453 views

Is there a workaround for lack of support of cascade triggers with MySQL?

As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a ...
0
votes
1answer
156 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 ...
2
votes
1answer
1k views

Update one table from another table while sorting that table based on one column

This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new ...
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
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
93 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 ...
2
votes
0answers
167 views

How to execute a non-table-locking update operation on PostgreSQL? [closed]

Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
0
votes
1answer
140 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 ...
3
votes
1answer
6k views

On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` ...
3
votes
1answer
3k views

Oracle: Non key-preserved table should be

I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
3
votes
2answers
6k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
0
votes
0answers
102 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 ...
2
votes
2answers
617 views

DB2 UPDATE TRIGGER

I am trying to create a trigger in a DB2 database that runs on the update of a column in one table, and then fills in another table with certain values. For example, there is a power unit table with ...
0
votes
2answers
2k 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 ...
1
vote
1answer
253 views

Update field1.table1 based on compound join between table1 and table2 - Oracle

I want to run update statement on Oracle 11g, but it fails with the following error: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery ...
2
votes
1answer
275 views

Apply in a CSV file to multiple databases

Recently, I have been working with a client whom has asked us to provide their dataset for use in a mobile application we are making for them. The requirements for this project include the ability to ...
2
votes
2answers
180 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 ...
1
vote
2answers
134 views

How does optimistic locking actually enforce re-read/update?

My understanding of optimistic locking is that it uses a timestamp on each record in a table to determine the "version" of the record, so that when the record is access by multiple processes at the ...
2
votes
1answer
1k views

Update to session table slow

I have a number of websites with session tables on a web server, these are used by Joomla CMS and Symfony framework systems. With slow_query_log=0.6, the update queries for the session tables come up ...
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 ...