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.
7
votes
1answer
412 views
Direct the OUTPUT of an UPDATE statement to a local variable
I would like to do this :
DECLARE @Id INT;
UPDATE Logins
SET SomeField = 'some value'
OUTPUT @Id = Id
WHERE EmailAddress = @EmailAddress -- this is a parameter of the sproc
Is this even ...
5
votes
1answer
2k views
Trigger to UPDATE after UPDATE?
I want to make a trigger to record the time of any update as
CREATE TRIGGER col_update
AFTER UPDATE ON col
FOR EACH ROW BEGIN
UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id
END
The ...
4
votes
2answers
3k views
Updating Multiple Rows with Data from Column A into Column B?
I have about 60,000 rows that I'm needing to update the information from column_a to column_b. For example, 60,000 customers need their arrival_time to match their departure_time. I understand that ...
4
votes
2answers
2k views
Update many rows in a table with a single statement?
What is the easiest way to update many rows in a table? I have a csv file that looks like this:
|primary_key |value|
| 1 | xyz|
| 2 | abc|
| 3 | def|
...
Rows with ...
4
votes
2answers
175 views
fast bulk incrementing in MySQL
I have one big table foobar describing a many-to-many-relation and containing millions of foo's, millions of bar's and every bar having several hundereds of foo's -> billions of rows.
CREATE TABLE ...
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 ...
3
votes
3answers
228 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 ...
3
votes
6answers
107 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 ...
3
votes
1answer
137 views
See actual results of SQL UPDATE command
Is there a way to get the actual results of SQL UPDATE command (ie the table rows themselves), not just get the number of rows (other than by converting the UPDATE command to a SELECT)? I'm using ...
3
votes
1answer
88 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
1answer
126 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
2answers
284 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 ...
3
votes
1answer
289 views
Update rank on a large table
I have a mysql table that has now 12 million records and I'm trying to update a column (rank) with the following UPDATE code:
SET @r=0;
UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY ...
2
votes
1answer
771 views
SQL Select WHERE row updated
USE [RF_WORLD]
GO
/****** Object: Trigger [dbo].[lastdeathtrigg] Script Date: 08/20/2012 10:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ...
2
votes
1answer
35 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 ...
2
votes
2answers
111 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
2answers
793 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 ...
2
votes
2answers
318 views
postgreSQL update set only where cast is possible and ignore error?
I have 2 columns in a PostgreSQL table. The mac_address_temp column is for migration from character type to MAC-address type:
mac_address | macaddr |
mac_address_temp | character ...
2
votes
1answer
57 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 ...
2
votes
2answers
126 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
...
2
votes
1answer
126 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);
...
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 ...
2
votes
1answer
92 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
1answer
121 views
Update ranking on table
I have two tables:
mysql> desc rank ;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
1answer
117 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 ...
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
0answers
59 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 ...
2
votes
1answer
126 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 ...
1
vote
4answers
344 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 ...
1
vote
3answers
85 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
4answers
10k 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 ...
1
vote
3answers
206 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
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 ...
1
vote
2answers
417 views
GROUP BY two columns
I want to count two columns, but need to get the results of first column in one row.
SELECT country, COUNT(*)
FROM table1
GROUP BY country, type
This query gives me
country type COUNT(*)
...
1
vote
2answers
203 views
Way to update many (>3000) records with unique data, using a character limited web form
Okay, as you can probably tell from the last part of the title, this is a non-ideal situation.
I'm using a piece of industry-specific web-interface software that I guarantee no one has ever heard of, ...
1
vote
1answer
342 views
Why I cannot let EXPLAIN update statements?
I can imagine that it's actually more important to explain SELECT statements, but I wonder why there's no possibility to use EXPLAIN on UPDATEs/INSERTs. I may use a WHERE-statement in an UPDATE where ...
1
vote
1answer
53 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
2answers
204 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
1answer
358 views
Find last (max) value according to TimeStamp using update method
I have task to find values of integer according to last (max) of timestamp (datetime) value.
Because it is so complicated query I am consindering this way to find last value something like this
...
1
vote
1answer
496 views
How should I update ID field values for all records based on previous values?
I need a SQL query that can update ID fields for all records in a table from their previous values to a new value based on that previous value.
For example, if the ID is 1, I want to change it to ...
1
vote
1answer
51 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 ...
1
vote
1answer
180 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 ...
1
vote
1answer
145 views
extremely slow MyISAM slave updates
I have 2 slaves and one is working as expected. The other slave is extermely slow while executing the "update" statements. The insert and deletes are fast. I used "check table tbl_name" to see if any ...
1
vote
1answer
206 views
What can cause an UPDATE statement to execute endless?
I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night.
Today it started but never endet.
So i've tried to run ...
1
vote
0answers
89 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 ...
0
votes
2answers
88 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
3answers
173 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 ...
0
votes
1answer
188 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
85 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
89 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` ...
