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
0answers
11 views

PostgreSQL Partitioned table update trigger

I have a date-partitioned table (inheritance partitioninng just like the one pg_partman does) which is referenced by quite a few tables in my database. To resolve the FKs checking for referenced field ...
2
votes
1answer
46 views

How to UPDATE by COUNT from JOINed tables

I have a schema as CREATE TABLE companies ( company_id int(11) NOT NULL AUTO_INCREMENT, company varchar(255), projects int(11), PRIMARY KEY(company_id) ) ENGINE=InnoDB CREATE TABLE employees ( ...
3
votes
1answer
22 views

UPDATE a column in one table according to a column in another

In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the ...
1
vote
1answer
42 views

Can I iterate a value depending if the previous is what I want?

Suppose that I have this table in PostgreSQL 9.4: CREATE TABLE trajs ( id serial NOT NULL PRIMARY KEY, obj_id integer, traj_id integer, geom geometry, t timestamp without time ...
1
vote
1answer
34 views

How to UPDATE NULL values by MAX()+1?

I want to assign values to null cells of a column as UPDATE table 1 SET number = MAX(number) + 1 WHERE number IS NULL but mysql return error Invalid use of group function.
0
votes
0answers
24 views

Update on a table on MySQL Innodb seems to hang / lock forerever

When performing an update on a MySQL database for an InnoDB table, the update sentence never finishes. This is the update sentence from the MySQL query log: UPDATE `bb_plan` SET `creation_date` = '...
0
votes
1answer
38 views

Insert into matching on a WHERE clause

I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (...
0
votes
0answers
14 views

On Duplicate Update Multiple Keys (not or) With And

I have friends table user_id_1 | user_id_2 | points 1 2 99 ADD UNIQUE (user_id_1, user_id_2) works perfectly, but i run this query; INSERT INTO friends (user_id_1,user_id_2) ...
3
votes
2answers
152 views

“Upsert” trigger writing mass duplicates from SSIS

I have a problem with an "upsert" trigger, being triggered by a SSIS package with fastload. (FIRE_TRIGGERS is enabled) CREATE TRIGGER [dbo].[MP_VOL_UPSERT] ON [dbo].[vwMP_VOL] INSTEAD OF ...
3
votes
2answers
50 views

Alter the letters after “ACCOUNTS\” to lower case?

I have a database table, users, with different username cases (see the example). How can I update the letters after ACCOUNTS\ to lower case? ACCOUNTS\James.McAvoy ACCOUNTS\michael.fassbender ACCOUNTS\...
0
votes
1answer
27 views

Event to update a column won't run

I have successfully created an event in MySQL to update a column called dob in a table called auth. My problem is that the event won't run. Here is my code: DELIMITER $$ CREATE EVENT ...
0
votes
1answer
23 views

Mysql Order , Select highest value item and update to another table

i have list in data_1 table. i want to select highest value item in one group and update it to another table. can i do that with 1 query? example : Select "Group B Leader Jane" and update it to other ...
0
votes
1answer
19 views

Change date precision values

Exposition: I have an oracle database table with date format as 'mm/dd/yyyy' and an unique constraint that contains the date column. My issue is that I can't seem to find a method to change the ...
2
votes
1answer
99 views

Update column with different value for multiple rows using a csv file

I often find myself using Excel to create update queries. When I need to update multiple rows with different values for a column, I simply use Excel to create the update queries (multiple). Here's ...
0
votes
2answers
44 views

Update Grouped records

I have the following query that returns duplicate values: SELECT [t].[Field1], [t].[Field2], COUNT([t].[Field2]) FROM [dbo].[Table1] AS [t] WHERE [t].[Field2] <> '' GROUP BY [t].[...
0
votes
3answers
67 views

Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script. INSERT INTO params (extension, type, subtype, paramvalue) VALUES ('12345', 'FRNR1'...
1
vote
2answers
76 views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | -----------...
2
votes
0answers
35 views

Postgres update query complaining about text value when only NULL values are passed

I am using Postgres 9.4.6. Is there any reason why the below update query would execute successfully here:- UPDATE "Predictions" SET "userid" = c."userid", "Fixture_No" = c."Fixture_No",...
0
votes
1answer
37 views

Update a valid column when another column has changed

We have a valid_phone column that just stores 1 or 0 for valid or not valid. When the user changes their phone number we want that field to go back to 0 so they will have to validate their number ...
1
vote
1answer
32 views

MySQL datetime to just date

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
0
votes
2answers
32 views

Update field in SQL Server Table Dynamicaly

I need to do a 1 time update of a Sql Server table. The table has a unique ID, contains a member number field and a FileName field which tells us what file the member data came from. What I am ...
0
votes
0answers
33 views

subscriber table needs to be updated - how to use a batch process AND how not to upset the replication while doing it?

I have a table called tblBOrderPaymentGiftVoucher that is replicated to 3 different subscriber servers. this table is part of a publication that contains many tables, some very big. I can't afford to ...
4
votes
1answer
19 views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 |...
0
votes
0answers
14 views

Track updated columns postgresql trigger?

I am using postgreSQL and I have tables on which I use triggers to get notified of changes on tables. Now, I have a usecase where when an update on a table is done, I want to notify only the updated ...
10
votes
1answer
507 views

Do databases make a delete and an insert when having to update rows?

So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something ...
2
votes
0answers
36 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
0
votes
1answer
15 views

Can't figure out how to update a column in one table based on multiple boolean values in another table in postgresql

What I want to happen is boolean milestone.milestonecompleted will turn true only if ALL of the linked boolean task.taskcompleted values are True I've looked around and come up with this. Any help ...
0
votes
1answer
72 views

Why can't I update or delete certain records in SQL Server?

I am having a weird issue in SQL Server 2014. I cannot update or delete certain rows from a table. I can insert rows fine, and select them, but when I try to update or delete a row, I get a timeout ...
0
votes
1answer
38 views

Updating column from another table - PostgreSQL 9.2 [closed]

There are two tables: ja_jobs junk.test_test1 I need to update the time_job column from junk.test_test1 to ja_jobs. Here is what I'm doing now: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM ...
1
vote
1answer
19 views

Remove accidental duplicates in PK table, cascade changes to FK table

Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and samples....
0
votes
2answers
33 views

MySQL - An UPDATE from a SELECT

The more I learn about about the great things you can do with mysql, the more I keep pushing to learn. I have two tables: Tests TestNumber (int primary key) InactiveTestSlotBitwise (int) ...
3
votes
2answers
51 views

MySql - Ranking so it only needs to be done once

By reading around, this seems to be a reasonable way of ranking people. The table: UserId (int primary key) Name (varchar(100)) Score1 (int highest scores rank 1) Score2 (int, if Score1s are equal, ...
3
votes
2answers
61 views

Multiple Update with Multiple Conditions

We are told to watch out for things being set to null (we dont want) by using the IN clause in the following: UPDATE Tests SET TestScore = CASE WHEN TestId = 10 ...
2
votes
2answers
73 views

Very tricky: Can't update a sequential list SQL-92 ( Filemaker)

I have a quite simple problem, but the solution is very tricky. I'm trying to solve this for months: There is a column "SortID" that needs to get updated with sequential integers, starting from 1. ...
2
votes
2answers
93 views

Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen

I have a table with about 8.5m rows in it. The table is tokudb and it has the indexes described below. I'm experiencing dismal performance when trying to run update statements like the following: ...
0
votes
0answers
52 views

Very Very Veeery slow insert queries

I am writing here because I`ve already spent days reading tutorials, other topics in here, and trying different configurations. The project : Fashion website with ~200k products which should be ...
0
votes
1answer
43 views

INSERT and UPDATE in Trigger

I have created a trigger so I could insert every row in a backup table CREATE OR REPLACE TRIGGER xyz AFTER INSERT ON table_1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO table_2 (...
1
vote
1answer
43 views

Mysql Update columns from same table columns

I have a table where payments are located with invoice_id and payment_id and also payment_due_date => payment receiving date and period_months that is total period of the payment I have added 2 ...
1
vote
2answers
49 views

UPDATE from subquery updates rows that shouldn't be updated

Using PostgreSQL, I want to update a column. The information how to update is in another table. My try was this, but it also updates rows I don't want to update: update table_1 set column_x = ( ...
0
votes
1answer
19 views

sql update query for two connected tables [closed]

UPDATE rooms set r.vacant=true FROM reservation s,rooms r WHERE r.room_no = s.room_no AND s.cus_id=1 I created above sql queryto update room status.I want to set vacant as true when customer_id =1....
0
votes
1answer
45 views

Updating data from subpartition when the subpartition name is unknown

Thanks to JSapkota who helped me pull the subpartition name as per below query select subobject_name from user_objects where data_object_id IN (select dbms_rowid.rowid_object(rowid) from TABLE ...
0
votes
1answer
34 views

Adding and populating column in Postgres with data from existing column

I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with ...
1
vote
1answer
52 views

Simple update takes forever

On SQL Server I want to update a 0/1 interger value for a specific set of records. For some reason this takes very long (let it run for 2 hours, still not finished). I can't figure out what is wrong ...
1
vote
1answer
37 views

Need help on UPDATE query [closed]

This is my table tracks id | art | time id is an auto-increment primary key art is a varchar(512) time is a timestamp not null predefined as current_timestamp and on update current_timestamp ...
-2
votes
1answer
58 views

Cannot update nor insert data to datetime field [closed]

I was trying to insert data through one web application I am developing and I could not insert data, while I was troubleshooting what the problem is, I saw that I can not neither edit datetime field ...
0
votes
0answers
33 views

PostgreSQL not running every update on my script

I'm running a dozen update statements on a loop and some updates just won't happen (they return 0 affected rows), If i recall the script it will slowly update the rows, I don't know how to debug this....
1
vote
2answers
46 views

Find times when table is least frequently used

I would like to analyse performance of one of the big tables in the database. There are only two types of operations on it. Inserts and reads. Once data is entered it is never updated. I need to ...
1
vote
1answer
136 views

SQL Server Trigger does not update all table records

This is my first question so please go easy on me. We are using Access 2013 as a front-end and SQL Server 2012 with MS SQL Server Management Studio 11.0.2100.60 backend with ODBC connections. I am ...