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.
4
votes
1answer
1k views
What's the most efficient way to batch UPDATE queries in MySQL?
I'm writing an application that needs to flush out a large number of updates to the database for an extended period of time, and I've gotten stuck at how to optimize the query. Currently I'm using ...
2
votes
0answers
101 views
Oracle UPDATE single record with multiple WHERE conditions
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 ...
0
votes
1answer
23 views
String replace using concatenated strings from various fields in PostgreSQL
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
43 views
SQL 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
54 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 ...
-4
votes
1answer
40 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
80 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
56 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 ...
2
votes
1answer
33 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
2answers
165 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 ...
-1
votes
3answers
99 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 ...
2
votes
2answers
90 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 = ...
2
votes
0answers
46 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
54 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
80 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
83 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 ...
0
votes
1answer
30 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
22 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
76 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
24 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
56 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
40 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
79 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 ...
3
votes
1answer
86 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 ...
0
votes
1answer
81 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` ...
0
votes
0answers
22 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 ...
1
vote
4answers
9k 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 ...
2
votes
2answers
78 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
77 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
50 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
86 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
125 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
70 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 ...
3
votes
2answers
281 views
Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?
In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records:
SELECT COUNT(id) shareLikeNo FROM ...
0
votes
1answer
196 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 ...
1
vote
2answers
199 views
Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped?
everything is in the question.
I have a table named tableA on which we have indexes and foreign keys and constraints.
I have to modify content in a column called column1. This being a production ...
1
vote
3answers
193 views
Problem with nested UPDATE queries
I cannot make an update query work. As a first step, the following one works correctly:
UPDATE Tab1
SET Tab1.a = '3'
WHERE
Tab1.id IN ( 123, 456 );
where id is the primary key of Tab1.
...
1
vote
1answer
52 views
Is it a good idea to have an index when the column value changes several times over record lifetime?
I have a table where one of the columns represents an object state - let it be State of type int. The State value starts with 1 and changes to 7 with values in between. Over a row lifetime there're ...
1
vote
0answers
87 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
177 views
How to re-arrange rows to best fit within groups?
I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order id num_items cat order_in_cat cumulative_sum folder
1 ...
0
votes
3answers
171 views
INNER JOIN Giving time out on large database
Getting time out on this script
UPDATE
uk_data AS ud
INNER JOIN
uk_pc AS up
ON ud.cat10 = up.WardCode
SET
ud.cat8 = up.Latitude,
ud.cat9 = up.Longitude;
uk_pc Table ...
2
votes
1answer
121 views
How to re-assign two variables in a conditional UPDATE
Consider a simple update as
SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5),
col2=IF(@var2>50, @var2 := @var2-col6, @var2);
...
0
votes
1answer
178 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
0answers
145 views
Can't UPDATE record with unique key [closed]
Im trying to do an UPDATE statement.
UPDATE `users` SET
`Email` = 'email@domain.com',
`Mobile` = '123456789',
`City` = 'Chicago',
`Firstname` = 'John',
`Lastname` = 'Smith',
`DOB` = '1980-01-01',
...
1
vote
4answers
338 views
Performance degradation while updating tables having 10s of millions of records
I want to update tables ( my be 20-30 ) having 10s of millions of records each.
The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
2
votes
2answers
774 views
Most efficient way to add a serial column to a huge table
What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)?
EDIT:
I want the column to be incremented values for existing rows (NOT NULL).
I didn't set a fillfactor ...
3
votes
1answer
124 views
A function to check if a column allows NULL
Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise?
I would be something like:
UPDATE mytable
...
3
votes
3answers
220 views
Update field with new string made from characters in existing value
field is meta_value and current value is like 01/01/2006
i want to change it to a string 20060101 so thought i could get the value and update it with the characters in the correct order.
ie
UPDATE ...
2
votes
1answer
121 views
I have to update all the rows of a Column in a table. Here is my query
So My tables are
People:
People_name(VARCHAR 100),
People_id (INT, PRIMARY KEY AUTO INCREMENT)
Tracks:
Track_id(INT PRIMARY KEY),
People_id_references (int ,Foreign key references ...
0
votes
0answers
111 views
Which update is faster using join or sequential?
My this question is in sequence of my previous question I asked.
I could write two solutions using Stored Procedure instead of trigger or nested-query .
Both use a helper function ...
