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.
0
votes
0answers
7 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 ...
2
votes
1answer
32 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
95 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
63 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
41 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
50 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
74 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
80 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
27 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
20 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
35 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
61 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
37 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
23 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
53 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
1answer
66 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
78 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
2answers
49 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
21 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
70 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
68 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
42 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
78 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
121 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
65 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 ...
0
votes
1answer
166 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
140 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
vote
3answers
164 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.
...
0
votes
1answer
51 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
81 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
3answers
164 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
104 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);
...
1
vote
1answer
174 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
1answer
157 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
133 views
Can't UPDATE record with unique key [closed]
Im trying to do an UPDATE statement.
UPDATE `users` SET
`Email` = '[email protected]',
`Mobile` = '123456789',
`City` = 'Chicago',
`Firstname` = 'John',
`Lastname` = 'Smith',
`DOB` = '1980-01-01',
...
3
votes
1answer
118 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
191 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
109 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
106 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 ...
2
votes
1answer
114 views
Update ranking on table
I have two tables:
mysql> desc rank ;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
...
1
vote
2answers
345 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(*)
...
3
votes
1answer
863 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
2answers
283 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 ...
1
vote
2answers
198 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 ...
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
721 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
348 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
83 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
173 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 ...
