New answers tagged

0

Got the solution: CREATE TRIGGER test_utrig ON test FOR UPDATE AS if UPDATE(id) and exists(select id from inserted where id not in (select id from inserted)) begin insert into test_a select 'update', UPDATE_BY(), GETDATE(), inserted.* FROM inserted


0

There's no FROM clause in UPDATE syntax. What I suggest you to do is an INSERT ... SELECT INSERT INTO documentRelNew (documentID, dataType, studentID, teacherID, vendorID) SELECT documentID, dataType, -1, dataID, -1 FROM documentRelOld WHERE dataType=2; I can't test this right now but I hope it helps to get you on the way.


0

You can add columns to the existing tables. These should be initially nullable until the data is loaded. After that you can alter the column definition to NOT NULL if appropriate. You can also drop columns from the table definition. The process I use is: Add new columns to table definition. Populate new columns. (Before using code that expects these ...


0

Well this did not work for me, the update just didn't happened even though there were matching rows. What I had to do is create the other table as subquery so temporary file is used. UPDATE tmContact INNER JOIN ( SELECT par.id, IF (LENGTH(contact.dynamicValues) > LENGTH(par.dynamicValues), contact.dynamicValues, par.dynamicValues) upd FROM tmContact par ...


0

Update Syntax for PostgreSQL is different: [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ FROM from_list ] [ ...


0

I try to avoid too much logic based on string operations. I think data can be divided in to two parts. One part where (a.Ename=b.Ename) Other part where (a.EName like b.EName||'.%') Here is the simple query I test works good: update OldData a set (a.job, a.sal) = (select b.job, b.sal from ...


-1

remove the index. update the column. return the index back. but if the column contains one and same value for all rows you can drop the index.


3

This should update for you as expected. Apologies for the number of character functions - I'm sure it could be improved via a REGEXP_SUBSTR call but I'm afraid I'm a bit busy at the moment. MERGE INTO olddata old USING (SELECT * FROM newdata) new ON ( old.ename = new.ename OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'...


0

if Ename columns are unique, this should work ; with old as ( select job , Sal , reverse ( substring(reverse(ename),charindex('.', reverse(ename), charindex('.',REVERSE(ename) )+1)+1, 50) ) as OldIdName from OldData ) , new as ( select Job , Sal , reverse ( substring(...


0

Here you go: update OldData a set (a.job, a.sal) = (select b.job, b.sal from NewData b where b.EName = a.EName)


1

Where My_ID = ID0001, ID0002, ID0003… IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<10,"ID000" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1, IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<100,"ID00" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1, IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<1000,"ID0" & Val(...


4

I did manage to do this through SQL. However, I think that this is a task that is far better better suited for TRIGGERs - i.e. if a record insertion/deletion is made on the project_map table, then a TRIGGER should fire adding +/- 1 to the relevant company's project field. I gave this a +1 because it was trickier than it seems initially. Giving the DDL was ...


-2

If you do not have space limitation, you can create a new table, the same as your table with your new column added to that table and delete the old table: create new_table as select old_table.*, (with or without default_Value) as new_column from old_table;



Top 50 recent answers are included