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)

3
votes
1answer
154 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 ...
0
votes
0answers
18 views

1175 during UPDATE in MySQL Workbench

I have this error in Mysql Workbench when I update table without using key in where clause but update query fire from my Application using connectivity of database surprisingly I have no error . ...
0
votes
1answer
29 views

Update part of timestamp field

I'm trying to fix a glitch in one of my tables. Several hundred records were given the wrong timestamp. The times are correct, but the date is wrong. I could write a little java program to fix it, ...
0
votes
1answer
58 views

SQL Server - Update using Join in Merge

Scenario : I need to update values in a table by joining it with another temp table. But I am using a Merge statement to check various scenarios and update the values in the table when the condition ...
1
vote
1answer
58 views

What is the most compact way to calculate and then Update a maximum value for each day's data?

I have a database that stores records for each bundle of material. A sample is taken from each bundle and sent out for assaying (for lead and sulfur). I receive the assay data and write the assay ...
2
votes
1answer
327 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 ...
5
votes
4answers
512 views

How can I avoid repeating the same condition in a “when matched then update” clause?

I want to merge one table into another. I need to apply conditional logic in my WHEN MATCHED clause, which would ideally be done like this: MERGE INTO ATable AS a USING BTable AS b ON a.ID = b.ID ...
1
vote
1answer
71 views

UPDATE id table on the columns with the same name

I may be a newbie question but I don't know how to resolve this. I have a table like: name | id | value A 1286487 1286333 B 1286489 1286403 C 1286495 1286455 C ...
0
votes
1answer
195 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), ...
0
votes
1answer
18 views

PostgreSQL SQL: updating summaries

I'm trying to optimize a query like: UPDATE master SET count = (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id) Where the master table is large, so running this results in a huge ...
15
votes
3answers
395 views

Slow update on large table with subquery

With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4. UPDATE [SourceTable] SET Bad_Count= ...
1
vote
1answer
31 views

How do I uniquely update values of a text column before applying UNIQUE constraint?

I need to apply a unique constraint to a TEXT field which may or may not already contain duplicate values. If for instance I have ABC twice, I don't mind renaming them as ABC-1, ABC-2, but what if I ...
1
vote
1answer
854 views

MySQL UPDATE query stuck on “Sending Data” State

I am having difficulty getting an UPDATE query to successfully execute, due to the process getting stuck on "Sending Data" state. I feel this differs from similar questions about the "Sending Data" ...
1
vote
1answer
59 views

SQL server UPDATE statement is processing records that should be eliminated by WHERE clause

I am getting a very odd error. Consider the table below. CREATE TABLE #MyTable ( Key1 INT , Key2 INT , x SMALLINT , y INT , z INT , a FLOAT , b FLOAT , c SMALLINT , s FLOAT ) -- insert ...
2
votes
1answer
36 views

Better way to update table with value from another table

I'm trying to rewrite the following query to make it more efficient: update c set c.cartype = (select Id from carTypes where carType = 'Coupe') from cars c inner join doorcount dc on c.Id = ...
2
votes
1answer
62 views

Best DB approach for a lot of updates

Recently I built an app that gets that from SVN repositories to run a few metrics. What I do is basically: 1) Read data from an existing SVN repository and save this data to a database (with only ...
1
vote
3answers
182 views

Make another column in a table equal to the primary key column

In my notes table (MySQL database) I have id as the primary key. I want the column originalid to be the value of the primary key. Here is the query I run after creating a record which I thought would ...
1
vote
1answer
1k views

How to add/update a column with an incremented value and reset said value based on another column in SQL

I am stumped, I have searched and researched until my browser tabs no longer show the close button ("X") until you hover over them. I tried rewriting the search term over and over but I guess I do not ...
4
votes
2answers
358 views

Adding hstore entries to an uninitialized (NULL) column

I got bitten by this "feature" recently. If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error. Is this expected? create table ...
12
votes
4answers
2k views

UPDATE performance where no data changes

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to ...
0
votes
1answer
41 views

Array of strings when updating a field

Unintentionally discovered that following query works in PostgreSQL: UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1; I'm passing an array of strings to field and the ...
10
votes
1answer
264 views

Why is my SQL Server query behaving differently on UPDATE than on SELECT?

I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great: DECLARE @RunDetailID INT = ...
2
votes
1answer
1k 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
1answer
1k views

Msg 5145: Autogrow of database log file took 1010871 milliseconds

In the past, I had an update query that ran for 7 days. I couldn't find a solution, so I created a new database with indexes. The update query has now been running for 26 hours. I am seeing messages ...
1
vote
0answers
702 views

MySQL - Truncated incorrect datetime value on update statement

I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC. (there will be apx 60 logs produced at 0.25 GB per day). I need to ...
0
votes
1answer
95 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 ...
2
votes
1answer
83 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
201 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
26 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
47 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
67 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
535 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
0answers
172 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 ...
3
votes
2answers
3k 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
28 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 ...
2
votes
1answer
133 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
38 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
2answers
70 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
0answers
52 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 ...
1
vote
1answer
153 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
284 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
496 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 ...
-7
votes
1answer
1k 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
275 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
97 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
1k 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
45 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
211 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
112 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
95 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 ...