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
3answers
117 views

Updating rows from other rows in a huge table

I have a huge table with items item-id | name | type | extra Many of them are "old versions" of type 0. I have to update the extra field in all new items (same item-id, but type > 0) with the ...
3
votes
4answers
914 views

Does updating a row with the same value actually update the row?

I have a performance-related question. Let's say I have a user with first name Michael. Take the following query: UPDATE users SET first_name = 'Michael' WHERE users.id = 123 Will the query ...
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
59 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 ...
5
votes
4answers
536 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 ...
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 ...
1
vote
1answer
76 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 ...
1
vote
1answer
1k 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
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 ...
15
votes
3answers
432 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
60 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 = dc....
2
votes
1answer
71 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
1answer
2k 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 ...
1
vote
3answers
240 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 ...
0
votes
1answer
43 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 ...
13
votes
4answers
3k 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 ...
1
vote
0answers
901 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
223 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 `trig_before_up_dad_records`...
2
votes
1answer
84 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
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
49 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
70 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
888 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
199 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
5k 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
29 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
160 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
1answer
96 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 ...
0
votes
2answers
71 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
61 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
189 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
203 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
3answers
667 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 '...
1
vote
1answer
328 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
116 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
2k 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
49 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
242 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
125 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
105 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 ...
2
votes
1answer
310 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
1answer
46 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 ...
6
votes
1answer
273 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 ...
3
votes
1answer
169 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
981 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
112 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, ...