1. I have multiple "ItemId" and have their parent/child data.
2. Need to update only child's "ItemOrder". Example, if parent has order "1.00" then their child will be 1.01 & 1.02... and so on.
Here, 1. for all ReportId, child items's ItemOrder = 0 2. example, for ReportId = 5, both child items ("Item1-Child1" & "Item1-Child1") of parent "Item1" has ItemOrder = 0
I need to, 1. update all child items with ascending numbers starts with 1 against each parent and each report. 2. for each different parent or different report, order by should starts with 1 again.
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and if the column has a string "d" then replace with "D" . I can write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz after the update: BcDxyz
Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
I have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes] @newBatches as dbo.CreateBatchList READONLY , @productId int , @cLevelRatio int , @nLevelRatio int AS set nocount on;
Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.
Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code FROM dbo.ADSCHL AD INNER JOIN TESTCEP FD ON AD.SCHOOL_NAME = FD.School_Name
ID Flag TestDate Value Comment 111 2 12/15/2014 7.5 null 222 2 Null 10 received
Matrix_Current table could have 1 or multiple rows as below.
ID Flag TestDate Value Comment 111 2 01/26/2015 7.9 111 2 02/23/2015 7.9 111 2 04/07/2015 6.8 222 1 null 8 test comment 1 222 3 null 9 test comment 2
When I run below update
UPDATE AM SET M.Flag = MC.Flag, M.TestDate = MC.TestDate, M.Value = MC.Value, M.comment = MC.Comment FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID
Matrix table has value below:
ID Flag TestDate Value Comment 111 2 01/26/2015 7.9 222 1 Null 8 test comment 1
I want to update Matrix table from all row from Matrix_Current, final table would like below:
ID Flag TestDate Value Comment 111 2 04/07/2015 6.8 222 3 Null 9 test comment 2
I've a table with two columns ITEMID and CHARGES. Multiple ITEMIDs with different CHARGES exists. I want to query TOP 10 CHARGES for each ITEMID in the table; how come i achieve this without typing the TOP 10 for each ITEMID in the table. The table has two many itemids and i want to view 10 for each with maximum charges.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1 set weight= d.weight, stateweight=d.stateweight, overallweight=d.overallweight from (select * from table2)d where table1.state=d.state and table1.month=d.month and table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
I'm having an Employee table with a Salary field. How can we increate thesalary of the employees with following conditions:1) salary between 1000 and 10000 : increase 25%2) salary between 10000 and 20000 : increase 15%3) salary between 20000 and 30000 : increase 5%Surely you can create a cursor to solve this. But the question is, Is itpossible to solve this in a single query, if no what is most optimizedway?
( @CompanyID NVARCHAR(36), @DivisionID NVARCHAR(36), @DepartmentID NVARCHAR(36), @ItemID NVARCHAR(36), @OrderNo NVARCHAR(36), @LineNo NVARCHAR(36), @TAllotedQty Numeric, @EmployeeID NVARCHAR(36), @Trndate datetime ) AS BEGIN By default iam passing 12 char itemid as parameter...
Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID END
Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same) IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID END
Updating the Table . I need to update Two Column .. DEPT and Product .
For a Same EmployeeName DEPT and Product Name should Repeat till Next Value in encountered.
Example For EmployeeName 'A' Dept Value 100 should Repeat till 20 comes, based on Date, because 100 comes before 20 and Project P1 should repeat till P2 comes.
Below is the Sample Source Table.
CREATE TABLE #MYTEMP3 (NAME VARCHAR(50), DEPT INT ,PROJECT VARCHAr(100), ORDERDT DATE) INSERT INTO #MYTEMP3 SELECT 'A',100,'P1', '2015-01-01' UNION ALL SELECT 'A','','', '2015-01-02' UNION ALL SELECT 'A','','', '2015-01-03' UNION ALL
[Code] ....
Looking for UPDATE Query . I do not want to use Common table expression to achieve this .
I have database with three tables Accounts, Results, and ClosedOrders. All are connected through AccountID PK/FK.
I got a wonderful select statement that gives me the latest Results for each Account.
WITH cte AS ( SELECT Accounts.AccountID, Accounts.AccountName, Results.ResultTime AS LastUpdated,
[Code] ....
I've been struggling to extend this with two more columns from the ClosedOrders table. How to add columns to the this view? Basically what I need is this:
SELECT SUM([Lots]) AS Longs FROM [DEV].[dbo].[ClosedOrders] WHERE OrderTypeID = 0;
SELECT SUM([Lots]) AS Shorts FROM [DEV].[dbo].[ClosedOrders] WHERE OrderTypeID = 1;
But it has to "join" the CTE somehow so that I get the correct answer for each Account row.
I would like to make an after update trigger on a table (ARD TABLE) where depending on the criteria the trigger will either insert, update, or delete a row in the trigger table. Basically, in the trigger table there can only be rows where the column status is > 0. So, if the ARD TABLE has a row updated that used to have a status of 0 and now the status changed to 1 then insert it into the trigger table. If the ARD TABLE had a row that used to be 1 and now it's 0 then delete it from the trigger table. And if the ARD TABLE had a row that was 1 and now made any change to that row we need to update the corresponding row in the trigger table.
I am trying to update all records in #newtable that exist in #mastertable. I have been using Intersect to show me the duplicate records, but now I need to update a field in #newtable This was my syntax to show the records that exist in both tables, how can I change this to an update statement? The field in #newtable I want to update is [alreadyexists] and I want to update it with a yes value
So update #newtable set [alreadyexists] = 'yes' select uno, mucha, pablo, company from #Newtable intersect select uno, mucha, pablo, company from #mastertable
I have a table that has two columns. One column has ID from 1 to 1800. The other column is null. I want to update the second column with values from 29 to 43. So for ID 1, value will be 29, ID value will 30 and it goes to 43. Then after it will start from 29 again and goes to 43. It goes all the way to the highest ID, i.e. 1800.I have added a script for better clarity
I have a below table, I have RowOrder for master entity and need to update child entity,example : if master has RowOrder 1.00 and there are 2 child before 2.00 , then child entites RowOrder will be 1.01 & 1.02 and so on...
I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.
I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my output ok but when I try to receive that output from write another select statement then its show error.
Msg 102, Level 15, State 1, Line 276 Incorrect syntax near ';'. Msg 102, Level 15, State 1, Line 315 Incorrect syntax near ')'. Msg 156, Level 15, State 1, Line 351 Incorrect syntax near the keyword 'as'.
I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].
