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)

0
votes
1answer
73 views

Create an “int” column forced between 0 and 3

I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible? My goal would be to have this column so that I could just execute a ...
1
vote
1answer
227 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 ...
8
votes
5answers
87k 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 ...
0
votes
0answers
19 views

How to update multiple tables in MS Access from multiple excel files? [migrated]

Every month I have to UPDATE my MS Access db from approximately 30 excel files. They have all the same structure and format. I try to modify this code several times to update each tables in my db in ...
0
votes
1answer
117 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
1answer
66 views

Single row update using primary key affects many rows

I'm doing a very simple datetime update in a table using its primary key: update TABLE set END_DATE = '2015-10-14 00:00:00' where ID = 1165027; I received the message (1 row(s) affected) 183 ...
0
votes
1answer
23 views

MySQL Trigger On UPDATE “Unknown Column” Error

I want to log a table when its updating a row which is column "insertDate" or "updateDate" value is before than 3 month. I wrote this trigger: DELIMITER /// CREATE TRIGGER ...
0
votes
1answer
23 views

How to make a mysql statement for sent log

I have a table called "make_sent_log" CREATE TABLE IF NOT EXISTS `make_sent_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `transaction_id` varchar(255) NOT NULL, `total_base` int(11) NOT ...
2
votes
1answer
21 views

Overwrite event - data from SQL Server to MySQL

In MySQL is it possible to execute an update query with values selected from external SQL Server database? I'd like to execute every night a timed query, which overwrites some data in MySQL with data ...
1
vote
1answer
36 views

How can I Rewrite Update Query to Update Using First Record From Inner Join?

I wrote up a question about trying to do an update with an inner join last week that got a lot of answers that I am currently trying out. Here is the link to the answer I used to get to the point I'm ...
3
votes
3answers
46 views

Problems updating with inner join with multiple on statements

I have an orders table on a database that contains geographical information about the city and state that the order is from. This includes latitude and longitude for doing haversine calculations. I ...
1
vote
1answer
205 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` >= ...
2
votes
0answers
8 views

Update a column based on other values in the row [migrated]

Consider the following trials table: CREATE TABLE trials ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name_A VARCHAR(6), name_B VARCHAR(6), score_A INT, score_B INT); Abstractly, this represents a ...
1
vote
0answers
28 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
2
votes
2answers
100 views

INSERT… ON DUPLICATE KEY UPDATE not working as I expect

I have a table called "Example" CREATE TABLE IF NOT EXISTS `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY ...
1
vote
2answers
26 views

Dynamically changing an order number to end in _ERR

I'm trying to update multiple orders at once instead of doing them one at a time. Is there a way to dynamically update them all at once? For example: I have 5 orders ABC, BCD, CDE, DEF, EFG. I need ...
0
votes
1answer
250 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 ...
1
vote
1answer
60 views

mysql update query almost 5 minutes slower in 5.6 vs 5.5

EDIT 6: I always thought an ALTER table recreated everything. When I add the optimizes shown in edit 5; it is always fast. (no more 4 minutes waits). The odd part is; if I am doing a lot with my ...
0
votes
1answer
32 views

How do I drop the TIME portion from DATEADD?

I am trying to create some test data where each Employee has a random birthdate. I accomplished the randomizing part but it did not return result in the format that I want.. NOTE: the DateOfBirth ...
0
votes
1answer
89 views

Increase SQL Server Performance

I have a fairly simple SQL Server 2014 Standard database with one main table. Here's the workload: I insert a row, do a few updates to it (up to 5 updates), and move on. I always update recent rows. ...
1
vote
1answer
536 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 ...
0
votes
1answer
115 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 ...
-1
votes
0answers
40 views

SQL Server - Parallel Updates? (or insert/ updates)

I know MySQL allows parallel transactions/ concurrency when it comes to "insert ... on duplicate key update". However I'm not sure how to achieve this in SQL Server. Whenever an update is being ...
1
vote
1answer
67 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 ...
0
votes
2answers
60 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
277 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
25 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
110 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
1answer
47 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
92 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), ...
2
votes
3answers
67 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
403 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
47 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 ...
3
votes
1answer
32 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 ...
2
votes
1answer
92 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
19 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 | ...
3
votes
1answer
36 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
43 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
45 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 ...
8
votes
1answer
14k 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
32 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
36 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
55 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 ...
12
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 ...
2
votes
0answers
42 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
83 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
54 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
32 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, ...
3
votes
3answers
208 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 ...