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)

1
vote
1answer
2k views

How to update a (very) large table without locking in MySQL

I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table. Unfortunately, whoever designed our schema didn't think things ...
3
votes
2answers
212 views

Updating large table worked before, now taking hours

BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not NULL;...
1
vote
1answer
33 views

Prevent UPDATE TRIGGER to fire when no changes made in SYBASE

In Sybase I have a main table test and an audit table test_a. The audit table is updated by triggers on insert, update and delete. Now the issue is, when I use update query with old data (no changes), ...
0
votes
0answers
23 views

Update Multiple Row's [migrated]

I would like to change one field of multiple row's at once by user input. Something like: I had some books at some branch, and i want to change some of them to other branch. The user select the ...
0
votes
0answers
15 views

MYSQL : Need Query How to update the NULL value for every next row of current row in MySQL table?

Example : Two table we can take, Table names are table1 and table2. Table1: It having 3 columns are id,date_s,value_s and the records are, 1 2005-01-01 25 2 2005-01-02 30 3 2005-01-03 10 4 ...
0
votes
0answers
39 views

Help with slightly complex ORACLE query

I have 2 tables: HEADER has the following field: IDENTIFIER, NOTE, GROUP_ID QUEUE has following field: PARAMETER, OPERATOR ... An example records would look like: HEADER: MY_STUDY_123456 (...
1
vote
2answers
310 views

How to update the parent/child of all rows in the tree (ltree)?

Here are my configurations: DB type: postgresql (9.3 if that's necessary) Table name: product_sections Column names: section_id (integer) & section_path (ltree) Detail: I have one reference ...
0
votes
1answer
515 views

MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

I had a table with ~0.5M rows and tried to exec simple query, something like UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999 but result was ERROR 1297 (HY000): Got ...
0
votes
2answers
19 views

How to convert from one type of relationship table to another type

I currently have a relationship table where it's: documentID, dataType, dataID Where documentID is the link to the document file. The dataType is if the data is a specific type of user, for example ...
0
votes
2answers
171 views

using update query with subquery in Oracle sql

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement ...
0
votes
0answers
14 views

Postgresql - How to use NEW in subquery?

I have a table that I want to update. I wanna use the NEW in my subquery where clause but error says, Missing from clause entry for table "new". Additional Info details table - serial, ...
1
vote
1answer
27 views

Remove accidental duplicates in PK table, cascade changes to FK table

Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and samples....
6
votes
2answers
12k 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
vote
0answers
35 views

UPDATE based on the occurence of the same string within two other columns

I am currently using Microsoft Access 2007. I wonder if there is a way to find whether two specific columns in one table contain the same (undetermined) string. The table may look as follows: ...
22
votes
2answers
17k 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 = ...
1
vote
1answer
19 views

SQL Update column with another table column

I'm using Postgres 9.4, and already seen others asking the same (simple) question, but theirs answers do not solved my problem and I can't see why. Suppose that I have these table A with common data ...
0
votes
1answer
373 views

Tracking the update or changes in mysql table

I need to stream the changes to elasticsearch to keep in sync with my database(mysql). What will be best way to do this ? I have thought for below approaches Using trigger( but my table will have ...
1
vote
4answers
78 views

Update data of one table from another with comparing columns have same initial strings

I have two tables: NewData: EName Job Sal smith clerk 2000 allen sales 2000 jones.domain.com Manager 6000 OldData: EMPNO ...
1
vote
1answer
195 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is ...
10
votes
3answers
864 views

Update all rows

I want to know the most efficient manner to update every row in an extremely large Oracle table for a single column. For example: update mytable set mycolumn=null; or: update mytable set mycolumn=...
0
votes
0answers
30 views

Update on a table on MySQL Innodb seems to hang / lock forerever

When performing an update on a MySQL database for an InnoDB table, the update sentence never finishes. This is the update sentence from the MySQL query log: UPDATE `bb_plan` SET `creation_date` = '...
0
votes
1answer
197 views

Auto increment form field

I am working with a database, with some forms. What I am trying to do is an auto increment field in my form. For example ID -- every time when I type some new record, this field ID should increment ...
1
vote
0answers
65 views

PostgreSQL Partitioned table update trigger

I have a date-partitioned table (inheritance partitioninng just like the one pg_partman does) which is referenced by quite a few tables in my database. To resolve the FKs checking for referenced field ...
2
votes
1answer
62 views

How to UPDATE by COUNT from JOINed tables

I have a schema as CREATE TABLE companies ( company_id int(11) NOT NULL AUTO_INCREMENT, company varchar(255), projects int(11), PRIMARY KEY(company_id) ) ENGINE=InnoDB CREATE TABLE employees ( ...
3
votes
1answer
24 views

UPDATE a column in one table according to a column in another

In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the ...
1
vote
1answer
48 views

Can I iterate a value depending if the previous is what I want?

Suppose that I have this table in PostgreSQL 9.4: CREATE TABLE trajs ( id serial NOT NULL PRIMARY KEY, obj_id integer, traj_id integer, geom geometry, t timestamp without time ...
1
vote
1answer
35 views

How to UPDATE NULL values by MAX()+1?

I want to assign values to null cells of a column as UPDATE table 1 SET number = MAX(number) + 1 WHERE number IS NULL but mysql return error Invalid use of group function.
0
votes
1answer
41 views

Insert into matching on a WHERE clause

I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (...
0
votes
0answers
15 views

On Duplicate Update Multiple Keys (not or) With And

I have friends table user_id_1 | user_id_2 | points 1 2 99 ADD UNIQUE (user_id_1, user_id_2) works perfectly, but i run this query; INSERT INTO friends (user_id_1,user_id_2) ...
3
votes
2answers
154 views

“Upsert” trigger writing mass duplicates from SSIS

I have a problem with an "upsert" trigger, being triggered by a SSIS package with fastload. (FIRE_TRIGGERS is enabled) CREATE TRIGGER [dbo].[MP_VOL_UPSERT] ON [dbo].[vwMP_VOL] INSTEAD OF ...
3
votes
2answers
51 views

Alter the letters after “ACCOUNTS\” to lower case?

I have a database table, users, with different username cases (see the example). How can I update the letters after ACCOUNTS\ to lower case? ACCOUNTS\James.McAvoy ACCOUNTS\michael.fassbender ACCOUNTS\...
2
votes
1answer
103 views

Update column with different value for multiple rows using a csv file

I often find myself using Excel to create update queries. When I need to update multiple rows with different values for a column, I simply use Excel to create the update queries (multiple). Here's ...
0
votes
1answer
40 views

Update a valid column when another column has changed

We have a valid_phone column that just stores 1 or 0 for valid or not valid. When the user changes their phone number we want that field to go back to 0 so they will have to validate their number ...
0
votes
1answer
27 views

Event to update a column won't run

I have successfully created an event in MySQL to update a column called dob in a table called auth. My problem is that the event won't run. Here is my code: DELIMITER $$ CREATE EVENT ...
0
votes
3answers
67 views

Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script. INSERT INTO params (extension, type, subtype, paramvalue) VALUES ('12345', 'FRNR1'...
6
votes
1answer
18k views

Update all columns from another table

I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this: update tableA set * =...
0
votes
1answer
24 views

Mysql Order , Select highest value item and update to another table

i have list in data_1 table. i want to select highest value item in one group and update it to another table. can i do that with 1 query? example : Select "Group B Leader Jane" and update it to other ...
0
votes
1answer
20 views

Change date precision values

Exposition: I have an oracle database table with date format as 'mm/dd/yyyy' and an unique constraint that contains the date column. My issue is that I can't seem to find a method to change the ...
0
votes
1answer
125 views

Update Column in table on midnight daily

I am designing a simple 1 table database in mySQL that will have two columns. One column will be the name of a specific restaurant on the webpage I will be using this database with. The second column ...
0
votes
2answers
44 views

Update Grouped records

I have the following query that returns duplicate values: SELECT [t].[Field1], [t].[Field2], COUNT([t].[Field2]) FROM [dbo].[Table1] AS [t] WHERE [t].[Field2] <> '' GROUP BY [t].[...
8
votes
1answer
20k 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` VARCHAR(...
1
vote
2answers
80 views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | -----------...
-2
votes
1answer
65 views

Cannot update nor insert data to datetime field [closed]

I was trying to insert data through one web application I am developing and I could not insert data, while I was troubleshooting what the problem is, I saw that I can not neither edit datetime field ...
2
votes
0answers
39 views

Postgres update query complaining about text value when only NULL values are passed

I am using Postgres 9.4.6. Is there any reason why the below update query would execute successfully here:- UPDATE "Predictions" SET "userid" = c."userid", "Fixture_No" = c."Fixture_No",...
0
votes
1answer
37 views

Adding and populating column in Postgres with data from existing column

I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with ...
1
vote
1answer
32 views

MySQL datetime to just date

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
0
votes
1answer
96 views

Update just one unused row

I have a table as follows: token | status | userid | serviceid -------+----------+--------+---------- token1 | ACTIVE | 1 | 2 token2 | INACTIVE | NULL | NULL token2 | ...
4
votes
1answer
21 views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 |...
0
votes
2answers
32 views

Update field in SQL Server Table Dynamicaly

I need to do a 1 time update of a Sql Server table. The table has a unique ID, contains a member number field and a FileName field which tells us what file the member data came from. What I am ...
0
votes
0answers
38 views

subscriber table needs to be updated - how to use a batch process AND how not to upset the replication while doing it?

I have a table called tblBOrderPaymentGiftVoucher that is replicated to 3 different subscriber servers. this table is part of a publication that contains many tables, some very big. I can't afford to ...