New answers tagged

1

I don't know much of T-SQL but I believe that to compare strings you need the logical operator AND. Try this: UPDATE StaffCopy SET StaffCopy.LastName=IIF(([LastName]='Avon' AND [FirstName]='Sarah'), 'Tyne', [LastName]);


1

First of all you need to make those rows unique, otherwise Oracle wouldn't know which value to choose. In SQL Server I would use something like this: WITH NewT AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY fileNo , folder, fileType ORDER BY (SELECT NULL)) AS RowN, fileNo, folder, fileType ...


1

Using the ranking ROW_NUMBER() function will work. First give row numbers to all rows in both tables, then join using these row numbers, then update: with oldt as ( select fileNo , folder, fileType, row_number() over (partition by fileNo, folder order by fileType) as rn from oldtable ), ...


3

You update column state inside the view. It refferences s.ClaimStatusName AS [Status] inside the view. From the code of the view we see that you update dimClaimStatus table (dimClaimStatus AS s). Seeing that you have 2 columns idClaimStatus and ClaimStatusName you have ID \ Name structure of the table. Inside the view you show ClaimStatusName. So you ...


7

General view updatability The key part of the CREATE VIEW (Transact-SQL) documentation is: Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. Note that even if the view is technically updatable, it may not be actually updatable in practice, due to limitations of the query ...


3

Imagine your UPDATE is updating s with the FROM clause of your view. Then read this blog I wrote recently to see how it could be affected. http://sqlblog.com/blogs/rob_farley/archive/2016/01/12/join-effects-with-update.aspx Assuming you're not breaking the rules for updateable views, then you should be okay. Just also be wary of the things in my post.


8

To just update an arbitrary one from each distinct group you could use WITH T AS (SELECT ROW_NUMBER() OVER (PARTITION BY [Finance_Project_Number] ORDER BY (SELECT 0)) AS RN, [Processing_Result_Text], [Processing_Result] FROM [InterfaceInfor].[dbo].[ProjectMaster] ...


2

The Query cache is a hindrance. (In general, it should be OFF for production systems.) All entries in the QC for that table will be purged by the Update. Millions of rows -- all of the table? A small fraction of a billion row table, but no good index? What? Let's dissect the query... Scan through the entire table. This takes time for the following ...


0

Triggers are procedural, so my inclination would be to perform the join and update as separate steps; this IMHO would make for simpler and more readable DML.This code should do what you want: DELIMITER $$ CREATE TRIGGER owed AFTER UPDATE ON bookings FOR EACH ROW BEGIN DECLARE dept VARCHAR(20); DECLARE rate DECIMAL(3, 2); IF ...


1

I've never handled anything like this before but why not do a combination of the +1 and the decimal versions. Do the decimal versioning while they are editing .. then at the end of an editing session (when they do a final save) go ahead and re-write all of the values as integers. Presumably in any given editing period there won't be so many edits that you ...


1

One syntax that can be used is the following. Instead of the syntax that is not working: update table1 set cola = a, colb = b where tkey in (select u.tkey, a, b from ... u ... -- some tables here, one of them is "u" ) ; This will do what is wanted (note that the where should be there, otherwise all the rows of table1 will be ...


2

I'm a big fan of MERGE. You can try the following; MERGE INTO table1 main USING ( SELECT TKey, a, b FROM table2 ) subsel ON (main.TKey = subsel.TKey) WHEN MATCHED THEN UPDATE SET main.ColA = subsel.a, main.ColB = subsel.b Here's a quick example; SQL> SELECT * FROM maintab; TKEY COLA COLB ...


3

@ypercube already provides ample explanation, so I'll cut to the chase what I have to add. If the parent_id does not exist, it should leave the generation column set to -1. I assume this is supposed to apply recursively, i.e. the rest of the tree always has generation = -1 after any missing node. If any node in the tree can be missing (yet) we need to ...



Top 50 recent answers are included