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

0
votes
2answers
55 views

Select statements during long update

I've a table MESSAGES id (long) | attach_data (image) | ins_date | flag this table contains all messages of my application. During night, my job cleans old attach_data with query update messages ...
0
votes
1answer
264 views

$addToset do not work in update if record not exists

I am trying to use following query db.TestColl.update({ "tags" : "silk" , "host" : "host.com"},{ "$set" : { "cat" : "MOVIE","publishTime" : 1370427540000 }, "$addToSet" : { "articleId" : { "$each" : ...
0
votes
0answers
16 views

PostgreSQL 9.3: Minimize locking when updating a large table with primary key

I am building a DB solution to handle a fast stream of online events. Each event UPSERTs a row in a location_update table, using its car_id primary key. Since there are very few new car_ids, the ...
2
votes
1answer
107 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 ...
1
vote
0answers
41 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 ...
1
vote
1answer
41 views

MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate?

Just curious on resource efficiency. If I have a list of inserts that need to be accomplished repeatedly which may contain duplicates (there is no way around this, the API I have to work with sucks), ...
2
votes
1answer
84 views

Update one column based on count of records of grouped value in another column

Given a table CREATE TABLE TABLE_ITEMS ( serial_num int null, item_group int null ); INSERT INTO TABLE_ITEMS VALUES (0,1), (1,1), (2,1), (3,1), (4,1), (0,2), (1,2), (2,2), (3,2), (4,2), ...
-1
votes
1answer
25 views

what is problem with time field in mysql during insert or update time? [closed]

I have created a time field in MySQL. During INSERT or on UPDATE, if I don't select the time then the time field gets a default value of 12AM. However, I want to set this time field to NULL by ...
0
votes
1answer
106 views

Update an array element with inc mongo update

HI All I have this Data in mongo, {"articleId" : [ { "articleId" : "9514666", "articleCount" : 1 } ], "count" : NumberLong(1), ...
2
votes
3answers
61 views

updating the entire column with datetime values based on other values in the row

I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic: Only add a value to ...
-6
votes
1answer
344 views

query to update status column with 1 to 0 and 0 to 1.? [closed]

Consider a table with a column Status containing values: 1 0 1 0 1 1 I need to write a query to update the Status column to change 1 to 0 and 0 to 1. How can I do this?
1
vote
1answer
25 views

mysql trigger / stored procedure - update another table row by row

I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this. this will be a little long, be patient please. First of all, I am trying ...
2
votes
1answer
26 views

MySQL not updating a row (zero row(s) affected) [closed]

I cannot figure out why MySQL 5.6.25 will not update an existing row: SELECT lcl_booking.file_number_id, lcl_booking.entered_datetime, lcl_booking.modified_datetime FROM lcl_booking WHERE ...
1
vote
1answer
215 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
2
votes
1answer
60 views

why update a table take so long on oracle

I have a table A which has 3800+ rows, another table B with 300+ rows. when I execute a update like this: update A set some_colum = '2' where another_column not in (select id from B) it takes 33 ...
2
votes
1answer
17 views

Update postgres row where condition is from another table

I want to update a password field from a given value that exists from another table. user_info: -------------------------------------------- | user_login | password | ...
1
vote
1answer
195 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
4
votes
1answer
27 views

Update columns values using select from external database and table in MariaDB trigger

I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using ...
1
vote
1answer
37 views

How to update row values with values from SELECT into another DB in a trigger

I have been working in a trigger for INSERT|UPDATE values for a given record based on same PK. What trigger basically should do it is, foreach row when some INSERT happens on veeva_new.account: ...
0
votes
1answer
39 views

One time data conversion in Oracle tables with excel data

First of all I have very basic knowledge in back end I need to do an one time data conversion in an Oracle table with a excel sheet data. Excel sheet have data in below format OldData NewData ...
0
votes
1answer
208 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 ...
8
votes
1answer
13k 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 ...
0
votes
1answer
30 views

Update only some records

I search an update-command, which update me only some records (more than one row) in a table. The update Information comes from an other table. Unfortunately it updates all 3500 records instead of ...
1
vote
1answer
32 views

Update query could not be bound

I'm trying to do a simple update query and I'm getting this error: The multi part identifier "customers.customer_name" could not be bound Query: Update Suppliers Set City = (select s.city ...
3
votes
1answer
45 views

Locking in Postgres for UPDATE / INSERT combination

I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once. The user needs to be able to redeem a coupon, which will insert a row into the log table ...
11
votes
1answer
4k views

Postgres Update, limit 1

I have a postgres database which contains details on clusters of servers, such as server status (active, standby, etc). Active servers at any time may need to fail over to a standby, and I don't care ...
1
vote
1answer
470 views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...
2
votes
0answers
34 views

How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions: CREATE TABLE products ( id bigint NOT NULL, title character varying(75), description text, manufacturer_id bigint, created_at ...
4
votes
1answer
64 views

MySQL 1175 Error While Using Primary Keys

What would cause the error 1175 that starts out with: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.... The problem I have is that I did include ...
3
votes
1answer
50 views

SQL Server: UPDATE permission for table variable

SQL Server 2012 I have a maintenance script that I wrote for some of my operators. They are members of the fixed db_datareader and db_datawriter roles. Part of the script creates a table variable, ...
2
votes
0answers
30 views

How to order by fields of joined table while selecting for update?

For selecting some rows of table Fwt_Chipkarte for update I use the following SQL statement (IBM DB2): SELECT o.Archiviert, o.Id, o.IdComm, o.Kartennummer, o.Konto, o.LetzteAenderung, ...
4
votes
3answers
182 views

INSERT/UPDATE in same query from previous SELECT with some singularities

I'm trying to perform a INSERT|UPDATE by selecting data from another DB and this is what I have so far: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,`first`,`last`,email) SELECT ...
0
votes
1answer
34 views

Apply operation on all schemas

I have an Oracle instance, with several schemas (all starting with the same prefix). PATT_A PATT_B PATT_C ... All of them have the same table, and I'd like to update all of them in one shot (with ...
2
votes
1answer
143 views

Update taking too long

I am updating a table with 250 million rows in SQL Server, and it's been running for 4.5 days. I'd like to use the computer it's running on for other memory-intensive work, so I'm torn about whether ...
0
votes
0answers
42 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 ...
3
votes
1answer
3k views

Oracle 10g: Optimizing an update query that uses a subquery

I have the following query within a stored procedure, and I've been trying to improve its performance to no avail. UPDATE TBL_REPORTING REP SET (REP.ISSUER_ID, REP.ISSUER_NAME ) = ( ...
2
votes
0answers
42 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
0
votes
1answer
37 views

Post installation of SSIS for existing SQL Server instance with different build versions

I would like to install SSIS into existing SQL Server 2014 Fail-over cluster instance with latest cumulative update 7 which was applied. I know how to do it but I want to know if consistency of ...
0
votes
1answer
52 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
1answer
27 views

always error “cannot insert value NULL” sqlserver

I want to update table "tmpiscunitmap.tr_kpi_id" year_period=2016 with value from table "tmp_kpi.tr_kpi_id where year_period=2016" with condition tmpiscunitmap.tr_kpi_id = tmp_kpi.tr_kpi_id on ...
0
votes
1answer
21 views

Table1 ID is auto increment and i also want to store the information from table 2 against that auto generated ID

This table has an auto increment ID on back end. "INSERT INTO `test`.`personal_info` (`username`, `email`,`password`) VALUES ('$username','$email','$password')"; This was for signing up of one ...
1
vote
2answers
632 views

Updating large table (~6.5 million rows) & indexes

I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all ...
0
votes
0answers
20 views

Updating values with randomly generated values?

I've got an .sqlite3 database here. I want to replace all the values of a set distinct number of columns in the database with randomly generated values (the values should be unique). At the moment ...
0
votes
0answers
28 views

subquery in set clause retrieving different values

I want to update the column "kstplcode" using different values coming from column warehouse except the value "main" with their corresponding delivery numbers. I've tried this code but the set clause ...
0
votes
0answers
22 views

How to reorder again' auto increment column' after deleting some rows [duplicate]

I have a MySQL db table, and first column is for id which has auto_increment. I want the first column be reordered after deleting some rows. iIs there any way to do that? id name 1 alex 2 ...
0
votes
2answers
47 views

How to access row value from UPDATE sub query function?

I would need to use UPDATE like this: update offer set (bank, bank_id) = (cost, gid) from ( select cost, osm_points.gid from pgr_kdijkstraCost( 'SELECT gid as id, source, target, walk_cost as cost ...
1
vote
1answer
37 views

How to update table1 field by using other table and function

I have two tables and one function: Table1 contains shop_code,batch_id shop_code| batch_id | regstriy_id 123 | 100 | 12 124 | 100 |13 125 | 100 |12 Table2 contains shop_code,shop_name ...
0
votes
0answers
23 views

PostgreSQL updates hang when two servers involved

I have an update statement that works fine when only a single server is involved, but never completes when it comes from two servers. I'm connecting with JDBC 9.1-902 to a 9.3 database on CentOS 6.6. ...
0
votes
1answer
21 views

How to update array fields in a table in Postgresql?

Say I have a table defined as following: CREATE TABLE my_tbl ( id bigint, a1 bigint[], a2 bigint[] ); I would like to create a stored procedure to append values to a1 and a2. Is this ...
0
votes
1answer
53 views

How to get number of locks acquired by my MySQL Query?

How to get number of locks acquired by my MySQL update query? Is it possible using MySQL Command Prompt or MySQL Workbench? Example query : update employees set store_id = 0 where store_id = 1;