Tag Info

New answers tagged

0

Unfortunately you can't use a trigger to update the table that called the trigger: from the documentation: A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. Could you move the admin field to a separate table which just holds the current ...


2

Thanks for updating that you are using Microsoft SQL Server, which does support Full Text Indexing. (As do many other versions of SQL servers.) First of all your query is using a WHERE (Word LIKE @partialWord) which means that if there is a prefix wildcard (e.g. '%tion') the whole table has to be scanned and the contents of the Word column likewise scanned. ...


4

We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten That's not the case. In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential ...


0

If you want to update each of the 4846 with a different CustomerCredit value, you will have to issue one query for each of the IDs: UPDATE [table] SET CustomerCredit = [credit-of-customer-with-id-40003] WHERE ID = 40003 ... UPDATE [table] SET CustomerCredit = [credit-of-customer-with-id-79870] WHERE ID = 79870 Depending on how you have access to ...


1

I am not fully clear regarding where you get the new CustomerCredit value but an SQL update should never change ID unless you explicitly tell it to. UPDATE [table] SET CustomerCredit = 100.0 WHERE ID = [customer-id-to-update] or setting all records to the same value, just omit the WHERE UPDATE [table] SET CustomerCredit = 100.0


1

I am not able to reproduce the issue with the code you have given. Am getting only one warning message in the line UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT) like invalid column name group_id(its because the column is added to the temp table dynamically) Can you recheck whether you are getting the issue with code you have given in ...


3

You need to perform the update in dynamic SQL too (or just create the table with all columns in the first place). The error is happening because the update with the new column is being parsed before the dynamic SQL has run. As an aside, you are probably only getting this error if you try to execute the stored procedure and choose 'Display estimated ...


0

I generally use PL/SQL collections for this. Essentially you create a table type, then create a variable with that table type, then do a select for update to get all of the data that you want to update, then do a forall update to update the data. Since you are updating the a table the record type should be that table. Such as: TYPE sku_tab_type IS TABLE OF ...


-1

4. or you can create an IDENTITY field CREATE TABLE LYNX.TRANS_AUDIT ( TA_ID **INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL**, TA_KEY_VALUE VARCHAR(100), TA_TABLE_CHANGED VARCHAR(40), TA_FIELD_CHANGED VARCHAR(40), TA_OLD_FIELD_VALUE VARCHAR(100), TA_NEW_FIELD_VALUE VARCHAR(100), TA_USER_WHO_CHANGED VARCHAR(128), TA_DATE_CHANGED TIMESTAMP, ...


3

Define all the foreign keys as CASCADE UPDATE If you have not done this, then you'll have to Create a new row with new primary key Update all child tables Remove old row .. in a transaction of course and watching out for other constraints that could fail


3

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint. If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update. EDIT: Since you do not have the ON UPDATE CASCADE constraint, but ...


4

You sure can. ON UPDATE CASCADE is what you are looking for. Here's a small how-to: http://sqlandme.com/2011/08/08/sql-server-how-to-cascade-updates-and-deletes-to-related-tables/ Basically, when you modify the PK, the cascade will go out and update all the FKs that reference it. This can be done in your CREATE statement, same as if you were doing a ...


0

Definately, exporting that many rows to a tool like Excel, sounds like the worst solution to what sounds like a normal action. This being : updating data. In the opening post, I don't see any reason why the data has to leave the Oracle database. Certainly not to a tool like Excel.


0

If document does not exists $addToSet will fail. When you initialize the document you need to use $push which creates the table and then $addtoset will work. I can't reproduce the error. mongos> db.test.update({name:"antonis"},{"$set" : { "cat" : "MOVIE","publishTime" : 1370427540000 }, "$addToSet" : { "articleId" : { "$each" : [ "20442242"]}} , "$inc" : { ...


5

A Fast Method A probably faster way to do this is the following: Provide the data in a database table Execute a MERGE statement that merges the data from this table into the source table To provide the data in a database table you can use sql loader or [external tables]. http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606 create ...


1

Instead of thousands of updates, you could insert the data into temporary source table and then run merge against the target table. merge into target_table t using source_table s on (t.id = s.id) when matched then update set t.column1 = s.column1, t.column2 = s.column2, ...;


0

Grouping a Primary Key is wasted time, since you get groups of one record only. Hence, the SUM function is unnecessary, you just need to get the column. But, the real problem you are trying to solve, is a bit unclear. If you ever have 2 tables in a database, with EXACTLY the same Primary Key, there is something wrong. Obviously, they should be 1 table ...


2

If you have a primary key, you don't have duplicate rows. In Oracle, you do multi-table UPDATEs by using MERGE, and not UPDATE, because that is inefficient. merge into table1 using (select col1, col2, col3, col4, sum(val2) sum_val2 from table2 group by col1, col2, col3, col4) table2 on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and ...


1

Size of the table is 53Gb without indexes. Obviously you have indexes, one of them a GIN index, which is typically multiple times the size of a plain table storing only the indexed column. I expect you get around 180 GB for SELECT pg_total_relation_size(osmm.topographicarea); Details on measuring sizes: Measure the size of a PostgreSQL table row As ...


1

Please move the SET a.field=(a.field+b.subfield) after ON a.id = b.subid and before WHERE So, the query should look like this: UPDATE table a INNER JOIN (SELECT subid, subfield FROM subtable WHERE ..... ) AS b ON a.id = b.subid SET a.field=(a.field+b.subfield) WHERE ... Give it a Try !!!


2

ASSESSMENT Your query is kind of dangerous to the MySQL Query Optimizer. I have an old post (Problem with MySQL subquery). The question posed involved this query DELETE FROM test WHERE id = (SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); Although the MySQL Query Parser will work with this query and accept it's syntax, the MySQL ...


0

If you are using InnoDB, You can secure your transaction with SELECT ... FOR UPDATE sentence http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html With this, you can perform an exclusive lock on the row while your transaction is not finished. Other sessions will wait until the row lock is released.


0

I would suggest reading this whitepaper on database version control from DBmaestro TeamWork (where I work) Basically, it discuses how database enforced change management combines the enforcement of version control processes on the database objects with the generation of the deployment script when required based on the version control repository and the ...


0

There multiple entry in table patient_encounter for a person_id, so you need to get the person_id with top 1 Try this update patient_encounter Set user_defined4 = 'DTC' where person_id = (SELECT top 1 person_id from patient_encounter where mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340') EDIT as per comment, if you are ...


1

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko) When you update an indexed column, changes must migrate through this architecture as follows: From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1 From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file) If you are ...


3

You can try Computed column rather than triggers, since its easy to modify . Try below query create table tab_computed ( Tab1_id int identity(1,1) primary key, Tab1_val1 smallint, Tab1_val2 smallint, Tab1_valInfo as (right('0000' + convert(varchar,Tab1_val1), 4)+ '-' + right('0000' + convert(varchar,Tab1_val2), 4)) ) insert into ...



Top 50 recent answers are included