I have multiple columns around 10...i am writing an update statement which doesn't update any of the rows
update table
set column11=isnull(columnA,0)+isnull(columnb,0)+...
where quarter='Q3'
also tried...
update table
set column11=SUM(COALESCE(column1,0) + COALESCE(column2,0))
where quarter='Q3'
both doesn't update column11.
I want to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1. It is some tricky because the value is NULL in some records of NewVol1 and NewVol2.
Update query. SAP, Code, Code1, Code2 and SAPdate are keyfield when join both table to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1.
Create table #Rec1 (SAP char(10), Code char(6), Code1 char(6), Code2 char(6), SAPDate datetime, NewVol1 char(20), NewVol2 char(20))
insert into #Rec1 values ('SATRIP','AAA','AA','A','2014-01-01','838.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','BB','B','2014-01-01','839.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','CC','C','2014-01-01','848.88484884',NULL)
insert into #Rec1 values ('SATRIP','BBB','AA','A','2014-01-01',NULL,'383.48377373')
insert into #Rec1 values ('SATRIP','BBB','BB','B','2014-01-01',NULL,'385.48377333')
insert into #Rec1 values ('SATRIP','BBB','CC','C','2014-01-01',NULL,'675.48377234')
[code]....
hi !!!!!!!!!!!!!!!!!!!!!!!
I have 3 tables
let say Employee,Employee_temp,emp_approval
i want to update employee table by selecting columns from employee_temp
i do that using oracle but i want it using sql server 2000
Sample syntax Below: for oracle
update employee e set (col1,col2,col3,col4)=
(select t1.col1,t1.col2,t1.col3,t1.col4 from employee_temp t where t.col1=:new.col1) where e.col1=:new.col1
this syntax for oracle....
plsease provide sql 2000 syntax and sql 2005 syntax please.........
thank you.
i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks.
Thank you,
Thomas
insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms)
SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#,
dbo.tblCustomersIOA.Active,
iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) ,
dbo.tblCustomersIOA.CreditLimit,
FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN
dbo.tblCustomersIOA LEFT OUTER JOIN
WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);
Hi,
I'm using IsNULL(<insert something SQL witty here>) to zero out a derived column if there's no return result. When I submit my query in Management Console the results come back perfectly, when I send it over as a stored procedure (as an alter or create) then execute, my last few columns come back 0 not populated as in the query run. Anyone know why this happens?
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
Hi,I have made the code to update more than 2 columns..It is a bad syntax, could someone help me improve my syntax?Code: command.CommandText = "UPDATE Items SET (Quantityavailable,numtaken) = (Quantityavailable - @qty,numtaken + @qty) WHERE productID=@productID"; command.Parameters.Add("@qty", TextBox1.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); The idea is to update Quantityavailable column by taking away the @qty value, and update numtaken column by adding the @qty column.It says there is a syntax error near '('. Thanks if someone can help,Jon
View 1 Replies View RelatedHow do I update all of the columns I want to update in one swipe?I can do them 1 at a time like this, but how do I update them ALL to = 0? Update Booth_Visitors_Data Set Hygienic_Actuators = 0 Where Trade_Show_Id = 34NFPA_ActuatorsCG5_CJ5Conveyor_Rail_AdjusterEX510FEP_TubingKQG_fittingsControlNetEtherNet_IPISE_ZSESVVQCWashdown_ProductsInstrumentationFittingsGTAPOEM_GTAPEmailed
View 4 Replies View Relatedassume my Table's Columns looks like below1 , Annie, History, pass, null 2 , Colin, Math , Pass, null7 , Annie, History, Fail, 111 , Colin, Math , Fail, 2When Delete 7 , Annie, History, Fail, 111 , Colin, Math , Fail, 2using the last column to check which row is reference with itso when delete Row 7 which reference with 1 , Annie, History, pass, null when delete Row 11 which reference with2 , Colin, Math , Pass, nullif I gooing to delete the row of No7 the result would be1 , Annie, History, Fail, null 2 , Colin, Math , Pass, 0 11 , Colin, Math , Pass, 0if I going to delete the row of No11 the result would be1 , Annie, History, pass, null2 , Colin, Math , Pass, null7 , Annie, History, Fail, 1I think the simply way to explan iswhen you delete a row of data , first find out which row is reference with it and then update the row which reference with it can you teach me how to do it ? wirte this request in Code or useing SQL Script?thank you
View 1 Replies View RelatedCan i use a multiple set in the update clause?
update CML.dbo.property
set total_Billed_Q1
=ISNULL ( dbo.property2.total_Billed_Q1 , 0 ),
set total_Billed_Q2
=ISNULL ( dbo.property2.total_Billed_Q2 , 0 )
FROM property
INNER JOIN
property2 on
property.financial_year = property2.financial_year
and
property.property_ref = property2.property_ref
Hi
I have to update the incidents table.
The initiator and initdept are two corresponding columns whoes value I have to get from another table Employee where (initiator means Empname and initdept means title)
if i just do
select Empname,title from employee
it gives me correct output while
select initiator,initdept from incidents
It gives the initiator correct but instead of giving the initdept
it gives output as initaiator the out put is given below
initiator initdept
Jeff C. Taylor Jeff C. Taylor
Randy S. Jonas Randy S. Jonas
Mike lewis Mike lewis
it should give the out put like
initiator initdept
Jeff C. Taylor Software Engg
Randy S. Jonas Tester
I hope you got my question!
now I hav to update incidents table such that it should give initiator as well as its corrosponding initdept
thanks
sambhav jain
I have a stored procedure that inserts values into five columns of atable. I need another stored procedure that will allow the user to passone or more of those parameters and update only the column for the datathat was passed. In other words, the update may only have one or two ofthe parameters that was originally provided in the insert. Therefore, Ido not want to update the columns that did not change. What is theproper way to handle this situation?
View 1 Replies View RelatedI need to create an SSIS package that updates columns in a table from columns in another database where the keys match. What's the best way to do this?
View 3 Replies View Relatedi there,
I've got a problem With updates on a Table.
I want to update only the Columns which are different to the values of my update-Statement.
One of the problems is an update trigger , which is fired on all update statements.
I want the trigger to be fired only when there are real changes.
Of course i can retrospectively check if there was a real, but i can't devide the real updatet colums from the non changed values. I know there is a statement like RowsUpdated but it doesn't work that way. It indicates also the non changed columns. To lock the row pessimistic read and compare the contents before update is also no option for me.
Is there any command to perform this in a smart way? Or should it be suggested for the next version of SQL-Server like "UPDATE CHANGES MY_TABLENAME SET MY_COLUMN= ... "
thanks in advance
Raimund
Hey ya'll...
I need to create a query that I can run that will allow me to essentially take the first initial and last name and combine them from two columns in one table and update one column with that new data in another table. Example of need:
UPDATE tblLogin.UserName with (first character(tblEmployee.FName)+(tblEmployee.LName)) WHERE tblLogin.EmployeeID = tblEmployee.EmployeeID.
That was TOTALLY pseudo code and I know I'll need a join statement in there somewhere. Both tables reside on the same database, such as this:
dbEMPLOYEE (Database)--> tblLogin (Table)----> UserName (Column)--> tblEmployee (Table)----> FName (Column)----> LName (Column)
Any help would be GREATLY appreciated! Hopefully something soon - this was handed to me a few minutes ago and I have a few minutes to get it done.iSheahan
Hi Guys
I need your help again, I am try to update several columns and the data type is 'money'.
Below is the code I have used:
UPDATE CAT_Products SET
UnitCost ='10.00',UnitCost2 = '10.00',UnitCost3 = '10.00',UnitCost4 = '10.00',UnitCost5 = '10.00',UnitCost6 = '10.00'
WHERE ProductCode = '0008'
But it will not update, instead I get this error:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>[Error] Script lines: 1-9 -------------------------- Disallowed implicit conversion from data type varchar to data type money, table 'dbo.CAT_Products', column 'UnitCost'. Use the CONVERT function to run this query.
More exceptions ... Disallowed implicit conversion from data type varchar to data type money, table '.dbo.CAT_Products', column 'UnitCost2'. Use the CONVERT function to run this query.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The error message indicates that I need to use the convert function. But the columns data type is set at 'money' not 'varcher' . So do I need to convert data type to 'varcher' in order to update and convert back to data type 'money' when update complete? Or do I need to indicate in the update statement that data type is already 'money'? I am not sure how I would either.
Thanks
Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help.
View 4 Replies View RelatedI am almost sure I can update variables columns in one select/case type
statement, but having problems working out the syntax.
I have a table with transactions - with tran types as the key.
in this example, types = A,B,C ,D.
in this first example I am updating the sum of QTY to value t_A based on
tran types =A.
can I perform sub query/case to update with the same where clause
but for types B,C and D?? I also have to insert for specific lot numbers each sum values.
Create table #t_reconcile(
t_lot_number int not null,
t_A float,
t_B float,
t_C float,
t_D float)
insert #t_reconcile
select t.lot_number, sum(t.qty)
from i , t
where i._id = t.event_id
i.transaction_type = 'A'
group by t.lot_number
order by t.lot_number
Hi
I have a 2 Tables EMP, STU
In EMP Table there is a Column "Country"
In STU Table there is a Column "City"
Where the EMPID = STUID
on this conditon how can i update those 2 columns
This is for one Table one column update how can i do 2 at a time
I don't want to do in 2 seperate UPDATE statements
UPDATE EMP
SET EMP.Country = 'USA'
WHERE EMP.EMPID = STU.STUID
Can some one can help me to fix this issue..
From
Madhavi
the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;
these are all numeric data types.
I have task where i need to update the 2 columns from 2 different tables. I need to get one column from one table and update to the other table.If the column name do not match....
View 4 Replies View RelatedCan you update data from multiple tables in the same UPDATE statement, by joining those tables in a CTE ?
For example, this fails:
DECLARE @UPDCATE_COUNT AS int = 100000;
WITH COMBINED_TABLES AS (
SELECT TOP (@UPDATE_COUNT) T.UpdateID, T.IS_UPDATED, U.[Description]
FROM dbo.Table1 AS U
INNER JOIN dbo.Table2 AS T
[code]....
Kairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?
Thank-you very much for your time and expertise,
Kairn"
Hello,I've written an insert trigger to fill in data on 5 columns based on the keyfield column after a record is added to a table. The trigger works fine.But what I also want to do is to write a stored procedure that will updatethe 5 columns for the entire table based on the table key field column. I'mnew to both triggers and stored procedures and I can't figure out how tomake a stored procedure do what I want.Can someone help me to get me started? Here is the trigger code that I amtrying to get to work as a stored procedure. All I'm trying to do is tobreak up an entry that contains dashes into separate fields that aredelimited by the dashes. As an example if field CABLENO is equal toI-IJB-200-45, then I want to break that up into 4 separate fields containingI, IJB, 200, 45 respectively.Thanks,Al Willis--------------------------------------------------------------------------------------------------------IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='CABLE_SEGMENTS' AND TYPE='TR')DROP TRIGGER IM.CABLE_SEGMENTSGOCREATE TRIGGER CABLE_SEGMENTSON IM.CAB_MFOR INSERTASDECLARE @CABLENO_REMAIN VARCHAR(40),@DASH_POS SMALLINT,@SEG1 VARCHAR(40),@SEG2 VARCHAR(40),@SEG3 VARCHAR(40),@SEG4 VARCHAR(40),@SEG5 VARCHAR(40)SELECT @CABLENO_REMAIN = CABLENO FROM INSERTEDSELECT @SEG1 = NULLSELECT @SEG2 = NULLSELECT @SEG3 = NULLSELECT @SEG4 = NULLSELECT @SEG5 = NULLSELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG1 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN = SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG2 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN = SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG3 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN =SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG4 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN =SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @SEG5 = @CABLENO_REMAINENDELSESELECT @SEG4 = @CABLENO_REMAINENDELSESELECT @SEG3 = @CABLENO_REMAINENDELSESELECT @SEG2 = @CABLENO_REMAINENDELSESELECT @SEG1 = @CABLENO_REMAINUPDATE IM.CAB_M SET CABLESEG1 = @SEG1,CABLESEG2 = @SEG2,CABLESEG3 =@SEG3,CABLESEG4 = @SEG4,CABLESEG5 = @SEG5FROM INSERTED INSWHERE IM.CAB_M.CABLENO = INS.CABLENOGO--------------------------------------------------------------------------------------------------------
View 2 Replies View RelatedHi,I have a table with the following columns:ID INTEGEDR,Name VARCHAR(32),Surname VARCHAR(32),GroupID INTEGER,SubGroupOneID INTEGER,SubGroupTwoID INTEGERHow can I create a rule/default/check which update SubGroupOneID &SubGroupTwoID columns when GroupID for example is equal 15 onMSSQL2000.It is imposible to make changes on client, so I need to checkinserted/updated value of GroupID column and automaticly updateSubGroupOneID & SubGroupTwoID columns.Sincerely,Rustam Bogubaev
View 4 Replies View Related
Hi, I need to update a number of columns in a number of tables - I just don't know how many. In this case, I am updating all varchar fields and nvarchar fields to be converted to lower case. The problem is that the table structure is amended over time as columns are added programmatically, so I do not know which tables have which columns and if so which of them are varchars.
I can get a table of which fields I need to update using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and I can do the update with UPDATE tblxxx SET column = LOWER(column)
But what I don't know is how to step through my temporary table and do the updates. I can do it in ASP.NET, but that involves pushing commands and data between ASP and SQL, and will be too slow. How do I do it in SQL?
Thanks,
Dave Stephens
Hi,
I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.
I know that I can get a lit of columns using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and do the update using:
UPDATE tblABCDE SET column = LOWER(column).
In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.
If so, how do you do it?
Thanks,
Dave Stephens
Hi,
I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.
I know that I can get a lit of columns using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and do the update using:
UPDATE tblABCDE SET column = LOWER(column).
In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.
If so, how do you do it?
Thanks,
Dave Stephens
Hi, all here,
I am having a question-is it possible to update several columns using CASE expression in SQL language? like if I wanna set each CASE for each column.
Thanks a lot in advance for any help and guidance.
Hi,
for example i need to update table with top 5callers who called the most to one number:
With subselect i get results:
Number Caller Times
555-11111 555-11112 10
555-11111 555-11113 9
555-11111 555-11114 8
555-11111 555-11115 7
555-11111 555-11116 6
And now i need update table and get this results:
Number Top1Caller Top1Caller Top1Caller Top1Caller Top1Caller
555-11111 555-11112 555-11113 555-11114 555-11115 555-11116
And i need to do this with every number in database
I have a simple question that is more about performance on my code for updating a column with the sum of two columns:
Example
Table = dbo.adding
c1 c2 c3Sum
1 2
3 5
4 6
Now if I just simple write
Update dbo.adding
set c3SUM = c1 + c2
This works, but there is a null in c1 or c2 then I know I can write this:
if exists (select * from dbo.adding where c1 is NULL)
Update dbo.adding
set c1 = '0', c3SUM = '0' + c2 where c1 is NULL
if exists (select * from dbo.adding where c2 is NULL)
Update dbo.adding
set c2 = '0', equals = c1 + '0' where c2 is NULL
Update dbo.adding
set c3SUM = c1 + c2
This works as well, but I am not sure how perficient it is being that from my understanding the code will evaluate the table three times and update it based on the criteria. If the table consists of 100,000 rows then this might not be a good solution
Is there a more proficient way to do this... I personally try to as little coding as possible to help increase performance. I know I can make this very complex and declare variables and put everything in a loop, but I personally think that would be less profient..
Hi,
I'm using sql-2005.
I want to update several columns in different tables whenever an update is happend on specific table. I have a trigger for that which update all the relevant places when it fires.
I want to do this update only if certains columns were changed, otherwise - do anything, to reduce performance.
How Can I know which columns were updated inside the trigger? The tables has many columns and only if 2 of them were changed then I need to update other tables.
Many thanks,
Nira.
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?