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

0
votes
1answer
9 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = ...
0
votes
1answer
19 views

MySQL Update Multiple Rows in Large Table

I have a large InnoDB MySQL database table. I would like to run this query against the database table: Update myBigTable Set someColumn=x WHERE userId = 7 OR userId = 88 OR userId = 22 OR userId = ...
0
votes
2answers
21 views

How can I maintain old information after an update?

I am having an issue figuring out a solution to not lose old information. For example, customers can place many orders. Let's a customer, Mary, makes her 1st order in 2013. Fast-forward to 2014, ...
0
votes
1answer
33 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 ...
1
vote
1answer
27 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 ...
2
votes
1answer
32 views

Create a new table (multiple columns) by selecting random values from an existing table

I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows): table1: id 754 800 330 4 59 My goal is to create another table2 with two columns ...
1
vote
1answer
22 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 ...
0
votes
0answers
13 views

How do I stop views (large data sets) from freezing my database

I am working with tables that contain upwards of 200,000 rows. My boss wants to use views that refer to other views in order to have things be more automated. The problem is that the MySQL server ...
3
votes
2answers
82 views

How to update ID=null values in table to incremental counter values?

On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows: ID NaturalID Comment 1 ...
1
vote
1answer
45 views

MYSQL - UPDATING multiple rows with different values in one query (for dummies)

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand. For instance, two updates ...
0
votes
1answer
37 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
17 views

Mongo DB - reading during update

Is it possible in a three-member-replica set (1 primary, 2 secondaries) to update records on the primary, or even delete several records and insert the updated data, and still execute read-ops on the ...
2
votes
0answers
33 views

ON DUPLICATE KEY UPDATE faster than UPDATE

I have a table with about 17 million rows: mysql> describe humans_we_respect; ...
3
votes
1answer
99 views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
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
2answers
68 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
65 views

How to support multiple updates in this trigger?

Below is the code of a trigger, basically it calls a function in case the record that is being updated does not have other row associated by it's Id in another table. In a multiple row update this ...
0
votes
1answer
59 views

phpmyadmin not working after mysql update

i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal. But now there is another ...
0
votes
0answers
18 views

Oracle after insert trigger vs update performance in plsql block

I want to know whether an after insert trigger is better than an insertion statement followed by update statement in plsql block.
0
votes
1answer
75 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 ...
1
vote
1answer
67 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
2answers
111 views

Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app. I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: ...
0
votes
1answer
69 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` >= ...
3
votes
1answer
314 views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
0
votes
1answer
29 views

How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE

Consider a table with the following columns: phone BIGINT NOT NULL PRIMARY KEY, name VARCHAR(64), state VARCHAR(64) We would like to partition the table on state, however that would require ...
0
votes
1answer
33 views

How to update a column in a table with data from another, unrelated tables columns data jus in order it selected?

There are two tables that unrelated. I want to update a field in first table with data that selected from second table in some order. There is no relations between tables, fields and data. I have just ...
2
votes
1answer
237 views

Update all columns from another table

I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this: update tableA set * ...
0
votes
1answer
45 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
0answers
26 views

Access 2013 compare changes made to table

I am currently working on an Access 2013 database and trying to find the best solution to a problem I am having creating it (I am fairly new to databases so bear with me). Situation: Data is imported ...
0
votes
1answer
72 views

update variable from multiple tables in one row

I have two databases, and I need to UPDATE variable 'birthday' from one row to another checking 'name' (from db1 to db2). The problem is, that 'name' variable in second db is located in another table ...
0
votes
1answer
112 views

UPDATE CONCAT without duplicates

I would like to concatenate multiple values ​​and remove duplicates (case-insensitive) in the same field with SQL only. Without using a unique key or stored procedure. Ex. ...
0
votes
2answers
66 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 ...
2
votes
1answer
114 views

How to reuse an update trigger for multiple tables in postgresql?

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table: CREATE TRIGGER ...
2
votes
1answer
39 views

Update only the oldest entry in a table with foreign key criteria

I'm working on a forum website. What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts". "Threads" "Posts" I'm ...
1
vote
2answers
56 views

Oracle tables partition related

Here is my doubt. Is it possible to apply a partition to one table depending in the partitions values of another one? Why I am asking? I have one table where information headers are persisted, and ...
2
votes
2answers
171 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
1
vote
1answer
123 views

Free space from image column after update

I'm doing some cleanup on one of our database for development purposes on a test server. There are a couple of tables with one IMAGE column. Both tables take roughly 50Gb of space, being the image ...
0
votes
0answers
66 views

How do I perform a batch update on the first n matching rows?

I need to perform a large number of updates at the same time. Each update needs to only affect up to a specific number of rows even though the WHERE clause might match to many more rows. So far, I'm ...
3
votes
2answers
249 views

Insert RECORD into a table in a trigger function

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table ...
0
votes
0answers
54 views

Multiple update of records in the same table

I have an update query. E.g. UPDATE table SET name = ? WHERE id = ? Now I have all the relevant ids collected. What I am doing is looping over the ids and execute the update query for each one. E.g ...
1
vote
2answers
1k 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
74 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
74 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
15 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
106 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
99 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
72 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
93 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
85 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
416 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 ...