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

2
votes
2answers
8k views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
0
votes
1answer
156 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 | ...
2
votes
1answer
135 views

Does MySQL do UPDATE in place on NOT NULL column?

Assume I have a table like this in MySQL: CREATE TABLE Foo ( Key INT NOT NULL , MyCol INT NOT NULL PRIMARY KEY (Key)) And I execute a statement like this on: UPDATE Foo SET MyCol = 13 WHERE Key = ...
0
votes
0answers
19 views

How to categorize pairs of tags?

I have a table containing pairs of tags (say html tags) such as id tag tag_match 1 <div class="first"> 1 2 <div class="second"> 2 3 </div> ...
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 ...
1
vote
3answers
111 views

How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

I'm working on PostgreSQL and I have a table like this called words_table: I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
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 ...
1
vote
2answers
109 views

Is column order reliable in UPDATE?

Can we reply on the order of columns in an UPDATE? SET @x = 0 UPDATE table1 SET col1=IF(col1 IS NULL, NULL, @x:=@x+col1), col2=IF(col2 IS NULL, NULL, @x:=@x+col2), col3=IF(col3 IS NULL, NULL, @x:=0) ...
1
vote
1answer
117 views

How to do two actions in conditional UPDATE?

In a query as SET @x = 1 UPDATE table1 SET col2 = IF(col1 = 'y', @x:=0, @x:=@x+1) Every time reaching the value of y in col1, I want to SET the @x:=@x+1 and after that reset the x to 0. In other ...
3
votes
4answers
1k views

Update column with the sum of column in previous rows in MySQL

We have a table, essentially: id, amount, end balance, account_id I now want to re-calculate the end balance values (because of possible corruption) End balance is basically "end balance of ...
1
vote
1answer
711 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
2k views

UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred? UPDATE table1, (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B SET table1.Freq = ...
0
votes
0answers
101 views

How to retrieve pkey value in mysql/python on duplicate key update

I'm currently using _mysql library in python. it uses pkey(int, auto increment) value that's created when inserting data using function conn.insert_id() This is how the query is made. INSERT INTO ...
0
votes
0answers
921 views

SQLite UPDATE two tables from VIEW or TEMPORARY TABLE

Here is the query that doesn't compile just to show how it should look like. But since I can't do Multiple table UPDATE's. This fake query should be able to run the inner UPDATE's for both tables if ...
1
vote
1answer
261 views

How to UPDATE a column by COUNT of FK from another table?

How to extend this query for the entire table, to update all ids? UPDATE table1 SET number=( SELECT COUNT(*) FROM table2 where id=1 ) WHERE id=1 id is the PRIMARY KEY in table1 and FK in table2. ...
1
vote
1answer
102 views

numbering rows consecutively for a number of tables

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to ...
0
votes
0answers
72 views

Inserting and updating on same table

I am currently working on a database that aims to provide information regarding the amount of time people stay in a company's car-park premises. This is done using a table called "tbl_stay" which has ...
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 ...
4
votes
2answers
3k views

How can I update a json element in postgresql json datatype

I can't figure how can I update an element in a postgresql 9.3 datatype. My exemple the db CREATE TABLE "user" ( id uuid NOT NULL, password character varying(255), profiles json, gender ...
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 | | ...
1
vote
1answer
184 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, ...
0
votes
1answer
120 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
1
vote
1answer
391 views

Mysql update different fileds set depend on condition

I have two tables "a" : CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `i1` int(11) DEFAULT NULL, `i2` int(11) DEFAULT NULL, `ib` int(11) DEFAULT NULL, KEY `id` (`id`) ) ...
0
votes
0answers
281 views

Alternatives for UPDATE statement Oracle 11g

I'm currently using Oracle 11g and let's say I have a table with the following columns (more or less) Table1 ID varchar(64) Status int(1) Transaction_date date tons of other columns And this ...
0
votes
0answers
145 views

SQL queries are very slow

I have noticed that my queries are getting slower. Some update queries take about 48-50 seconds. Here's how it looks: [root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log # Time: 131113 17:09:48 # ...
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
98 views

Create constraint for stopping mass update to a table

I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.
1
vote
1answer
741 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
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 ...
2
votes
1answer
1k 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 ) = ( ...
0
votes
1answer
200 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. ...
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 ...
10
votes
3answers
4k views

How to optimization database for heavy I/O from updates (software and hardware)

The situation I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some ...
0
votes
1answer
457 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 ...
1
vote
2answers
209 views

Install SP 3 on SQL Server 2008, where merge replication is running

I have a SQL Server 2008 and I should install Service Pack 3 for it. On my server there are several databases replicated, and mobile devices sync data via merge replication. Can problems occur, if I ...
0
votes
1answer
783 views

Mysql update table using ubuntu terminal

I have a CSV file that has 2 columns Name Email I have a table in my database that has the same columns + some extra columns. Now I would like to update my db table using that csv file. I mean ...
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
145 views

MySQL Replace function throwing error

I am trying to update ^G (Ctrl G) in a table, below is scenario- CREATE TABLET TEST(COLA INT,DESCRIPTION VARCHAR(20)); I have crtl G in my data for description field, so to replace it with a \,I am ...
2
votes
2answers
3k views

After update triggers that deal with multi-row updates

Currently working on an database Audit project based on triggers that are fired on update on specific tables. The triggers write the changes into a table; information written are: table name, updated ...
0
votes
1answer
184 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 ...
-1
votes
1answer
59 views

What could I possibly be doing wrong with this update statement

UPDATE pcdbak SET pcdbak.Title = pcd.Title FROM pcd, pcdbak WHERE pcdbak.eCommNum = pcd.eCommNum This was the error message: [Err] 1064 - You have an error in your ...
1
vote
3answers
3k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
5
votes
6answers
1k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
0
votes
1answer
221 views

String replace using concatenated strings from various columns

I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order. The specification says: ...
4
votes
1answer
363 views

Oracle UPDATE followed by SELECT in LinqPad

I'm trying to UPDATE values in an Oracle database table (of salary information) that has no row IDs or a primary key. I'm writing these statements in LinqPad. This syntax should work just fine in ...
-4
votes
1answer
172 views

Update date Column values? any please help? [closed]

I Want to copy and data in same Table and Update date Column values? any please help?
0
votes
1answer
705 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

Error “#1118 - row size too large” on the first row of the table only

I ran into a problem using a mySQL database. I have some columns as type text but when I try to enter data into the first row I get the error code "#1118 - Row size too large. The maximum row size for ...
4
votes
1answer
3k views

UPDATE table based on the same table

I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same ...
-1
votes
3answers
113 views

Add Contents in a Column and make them 0 [closed]

http://sqlfiddle.com/#!3/96f11/3 In the above fiddle, I need the required output in the Fiddle. ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data ...