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
vote
1answer
36 views

Optimizing bulk update performance in Postgresql with dependencies

Basically my question is the same as this one, but WITH dependencies, so drop/renaming the table is not a trivial option (I assume). We are refactoring a large, poorly designed table which has many ...
1
vote
2answers
80 views

how should i do this trigger?

I am a newbie to the sql world :) Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ? Here is the code : DELIMITER ...
1
vote
1answer
713 views

How to use LOW_PRIORITY for heavy queries?

I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources. I do not want them to stop end users from ...
1
vote
1answer
186 views

Set replica set member host names on Secondary

I have a replica set rset1 that consists of a primary, secondary, and a backup server. I recently just changed their DNS names in Route53--the only name that didn't change was the Primary's, ...
1
vote
2answers
196 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
1
vote
1answer
746 views

UPDATE SET REPLACE() matches but does not change

I've seen another post about this exact thing but it's 1-1/2 years old so I thought I'd make my own...besides, the other post did not help me. What I am trying to do is pretty straight-forward. To ...
1
vote
1answer
198 views

How to UPDATE a table for the level of parents in a column?

I have a table with internal parents as id parent parent_level 1 NULL 1 2 1 2 3 1 2 4 3 3 where parent has a FK to id. How can I update the table ...
1
vote
1answer
558 views

Is there a workaround for lack of support of cascade triggers with MySQL?

As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a ...
1
vote
1answer
291 views

Update field1.table1 based on compound join between table1 and table2 - Oracle

I want to run update statement on Oracle 11g, but it fails with the following error: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery ...
1
vote
1answer
305 views

How to re-arrange rows to best fit within groups?

I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100. mixing_order id num_items cat order_in_cat cumulative_sum folder 1 ...
1
vote
1answer
271 views

extremely slow MyISAM slave updates

I have 2 slaves and one is working as expected. The other slave is extermely slow while executing the "update" statements. The insert and deletes are fast. I used "check table tbl_name" to see if any ...
1
vote
1answer
309 views

What can cause an UPDATE statement to execute endless? [closed]

I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night. Today it started but never endet. So i've tried to run ...
1
vote
1answer
7 views

SQL - Update table for a unique ID with multiple entries if one entry has variable in question

I'm trying to update a table to fill in a variable to each row in the table if one row for the unique ID shows that variable. I was trying to do the following, but it's giving me an error. ...
1
vote
1answer
50 views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
1
vote
0answers
67 views

Update on a small table times out

We use Microsoft SQL Server Express Edition with Advanced Services 2008 R2 SP2 We have two locations. Each location has one sql server that serves about 20 client computers. Client computers are ...
1
vote
1answer
50 views

How to update a single record in a table based of foriegn key ID in another table?

I am still very new to sql. I am having troubles updating one table. I am sending information from a web application into Microsoft SQL 2008 database. I have a table called EmployeePermissions that ...
1
vote
0answers
22 views

Compare While doing update [closed]

I have a to modify a Import job that checks values in an table that is only used for read operation and updates them if the Source has changed: Update mytable as Target Set c1 = Source.c1, c2 = ...
1
vote
1answer
126 views

Mysql sequential numbering based on MAX of another column

I've a table lp_pictures which have these fields: [id | plate_uid] [1 | aaa] [1 | aaa] [1 | aaa] [1 | bbb] [1 | bbb] [1 | bbb] and i need to UPDATE the field id to becomes: [id | plate_uid] ...
1
vote
1answer
4k views

Mysql: update query with subquery

Hello i need to perform the following query Query update Taxonomy set sourceId = ( select id from TaxonomyMapping a where a.oldId = ( select cm.en_ID from TaxonomyMapping ta ...
1
vote
1answer
117 views

scenario to update or insert big amount of data

I try to find the best scenario to update or insert 1 million contacts in my postgres database. My actual scenario is: read contacts 1000 per 1000 and try to insert multiple rows with one request ...
0
votes
2answers
8k views

Use CASE to select columns in UPDATE query?

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so: SELECT CASE WHEN val = 0 THEN column_x WHEN val = 1 THEN column_y ELSE 0 END AS ...
0
votes
2answers
80 views

How to update a column with values from the same newly inserted row on a SQL Server 2014 trigger?

Using SQL Server 2014, I have this: Table1 Tab1_id int identity(1,1) primary key Tab1_val1 smallint Tab1_val2 smallint Tab1_valInfo varchar(10) -- not yet implemented, will be used ...
0
votes
2answers
50 views

How To Ignore SQL Update Notification

I have a very weird situation. One of our SQL Servers has a software update notification pending for Microsoft SQL Server 2008 R2 Service Pack 1 (KB2528583). However, this server is already at SP2. ...
0
votes
2answers
72 views

For SQL Server, What is the proper way to update a table?

I don't have a mentor to ask so I am seeking direction from the DBA community. I am in charge of a SQL SERVER 2008 R2 and SQL Azure databases that are the resources for a website and an ERP system for ...
0
votes
1answer
76 views

Performance on updating row

When updating in SQL Sever, it is my understanding that the entire row is deleted and then recreated (which is why you get a new timestamp). So then, if you update 1 field or 500 fields, does the ...
0
votes
2answers
2k views

Update year alone in date (Oracle 11g)

When I checked one of my client's database, I found some mistakes which will make problems. The problem is that the date is showing as 01-01-01 when I try to display it in the dd-MM-yyyy format the ...
0
votes
1answer
31 views

Slow Postgres updates if OTHER columns are indexed?

Certain updates take far too long on large Postgres tables. Given these conditions: only one column is being updated, and it is non-indexed the column already has data in it for every row due to a ...
0
votes
2answers
142 views

Update statement is giving an invalid column name error, even though the column exists

I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement CREATE TABLE ...
0
votes
1answer
185 views

UPDATE statment with and without SELECT keyword

I have been doing some testing on removing the SELECT keyword from this update: Update #tmpDriverTime Set WorkDay2 = (Select IsNull(Sum(PyblHrs),0) From spr_DriverTimeEntry Where ...
0
votes
2answers
2k views

how to set a row's value from a certain row's value?

I have a MySQL table named "activities" with fields id(int), tstamp(timestamp), amount(decimal) balance(decimal). Decimal fields hold money values. id tstamp amount balance ...
0
votes
1answer
2k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
0
votes
1answer
50 views

Update and Insert Statement taking forever

I have a database with two columns that is called using the following query: SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC In my application it is ...
0
votes
2answers
42 views

update column based on sum of data from another table

I have two tables : Table1 (col1, col2, col3, col4, val1, status) Table2 (col1, col2, col3, col4, val2) For Table1 and Table2, The columns (col1, col2, col3, col4) are the composite primary key. ...
0
votes
1answer
201 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
0
votes
1answer
459 views

How to update a table by count from another table?

I have two tables as CREATE TABLE country_statistics ( id int(11) NOT NULL AUTO_INCREMENT us int(11), uk int(11), ca int(11), PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CREATE TABLE entries ( id ...
0
votes
1answer
707 views

Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2?

We have an test database configured with our web application. Due to internal web application error the entries were get updated in the database. Is it there any way to recover the updated records? ...
0
votes
1answer
3k views

update column based on the sort order of another query

I'm trying to add arbitrarily ordered records to a database so that they can be sorted by the ui (or when I query the database ). My problem is I already have the list, and I need to add a default ...
0
votes
2answers
27 views

Update all data in a table without affecting ID(PK)

I'm using SQL Server 2005. I have a table with 4846 customer records. The PK is called ID and spans from record 40003 to 79870. This numbers are used a other tables to hold info about user orders, ...
0
votes
1answer
67 views

Store the resulting list of a SELECT in a variable to count AND use the result

I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the ...
0
votes
1answer
21 views

Postgres blocking UPDATE on new column?

For the life of me, I can't figure out why Postgres won't let me issue this update statement below. Assume any table called dim_dates where there's a column dateType: ALTER TABLE dim_dates DROP ...
0
votes
1answer
28 views

postgresql updating integer column taking more space than expected

I have two tables, A(a1 serial, a2 varchar(8)) and B(b2 varchar(8)); My query looks like, alter table B add column b1 integer; update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = ...
0
votes
2answers
31 views

Updating a table with a single UPDATE statement vs several UPDATE statements

CREATE TABLE `messages` ( `mes_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'номер объявления', `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата/время дачи/изменения ...
0
votes
1answer
49 views

Check for blocked queries periodically and cancel them

I have series of update queries which are executed every 5-10 seconds. Sometimes it happens that one of these will block all the pending update queries as for some reason it just freezes. Since I can ...
0
votes
2answers
582 views

MySQL ON DUPLICATE KEY UPDATE performance issue

We have a table with over 200k rows. Every row belongs to a category, there are almost 50k rows per category. What is the best way to update all rows of that category? We are using a script like ...
0
votes
1answer
76 views

Update previous value field

I inherited a database that has multiple tables that contain some form of an object ID, a date, and a value. The metrics depend on comparing the current value on any given date to the previous value ...
0
votes
1answer
105 views

UPDATE Query replacing string primary key taking days to resolve

I'm working with a DB (not designed by me) that had the primary key set to a 47 character length string, which is causing problems due to the fact that the table has over 20 million rows, and this ...
0
votes
1answer
75 views

Postgresql condition on subquery results

This is a new question based on the solution to my previous question I have the following tables: users int (pk) user_id date last_visit int (fk) local_site sites int (pk) site_id site_stats ...
0
votes
1answer
157 views

Issues with 'plus symbol' output in PSQL on update query

Simple but frustrating issue... I'm trying to do an update command to correct a misspelling with this field: grouper=> select * from master_list WHERE group ~ 'JustAdopted'; group | ...
0
votes
1answer
147 views

How to UPDATE MAX and MIN of a GROUP BY?

I have a SELECT query of a table with GROUP BY id col1 category 1 some 1 2 x 1 3 another 1 4 x 2 // should be removed 5 some 2 6 x ...
0
votes
1answer
3k views

INSERT / UPDATE to merge data, when empty data is an empty string

Consider a table of some family members: mysql> SELECT * FROM some_table; +--------+--------+--------+ | f_name | l_name | work | +--------+--------+--------+ | Meirav | Cohen | school | | ...