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
126 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
16 views

How to minimize the writes needed to update a counter field multiple times

I need to minimize the number of queries that are executed just for updating a counter field. One of my example counter fields is raised by 1 everytime a page is visited, I was wondering what could ...
0
votes
1answer
61 views

Trigger on a Schema

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one. --Database level trigger. ...
3
votes
2answers
8k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
2
votes
1answer
175 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
1answer
98 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
32 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, ...
1
vote
1answer
60 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 ...
3
votes
2answers
288 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 ...
0
votes
1answer
33 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 ...
7
votes
5answers
65k views

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT ...
0
votes
0answers
35 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 ...
1
vote
1answer
7k 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 ...
2
votes
2answers
6k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor ...
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
85 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
116 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
0answers
73 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
110 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
146 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] ...
11
votes
3answers
5k 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
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 ...
1
vote
2answers
341 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
272 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
1answer
40 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
71 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 ...
1
vote
3answers
4k 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 ...
2
votes
1answer
4k 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
88 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
287 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
2answers
78 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 ...
3
votes
1answer
246 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
2answers
227 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 ...
2
votes
1answer
57 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
85 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 ...
4
votes
1answer
9k views

On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` ...
5
votes
3answers
8k views

Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn. First, I do this: select max(ora_rowscn) from ...
1
vote
1answer
503 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
118 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
569 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 ...
1
vote
1answer
127 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
0answers
124 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 ...
2
votes
2answers
12k 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
941 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
1answer
213 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 | ...
1
vote
3answers
117 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
164 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
77 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
112 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
1k 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 ...