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
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 ...
2
votes
1answer
764 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
-- ...
7
votes
1answer
395 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 ...
0
votes
0answers
85 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 ...
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 ...
1
vote
1answer
332 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
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
780 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 ...
0
votes
1answer
118 views
Updating a table based on condition
I have a database having a staging_table.
From this table, entries are added to other table named Profile.
Whenever staging_table is updated, it checks whether all its entries are present in Profile, ...
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 ...
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 ...
3
votes
1answer
285 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 ...
0
votes
0answers
53 views
SQL to update incrementall
Have a table photos
photos.id
photos.user_id
photos.order
A) Is it possible via a single query to group all photos by user and then update the order 1,2,3..N ?
B) added twist, what if some of the ...
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
2answers
196 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
4answers
341 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
1answer
353 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
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 ...
3
votes
1answer
136 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 ...
1
vote
1answer
492 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 ...
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 ...
