Good morning,
I want to update two columns in the same time by making a select from another table. Some think like the following;
Update TBL1 o set (o.COL1,o.COL2) =
(select f.COL1,f.COL2 from TBL2 f where f.PKY = o.PKY)
where o.COL3 = 100;
Thinks to helping me .
Hi All,
I am facing a problem to update the columns.
The issue is ..I am having 340 tables in SQL database with a total of 8500 columns.
The descriptions for all the columns in an excel sheet.
Is there any means by which we can run a query that which loads the descriptions to all the columns instead of doing that manually?
Hi,
I am working on a SQL Server table designed by a partner company and cannot change the data structures. There is a table with a list of people available for calling out to a security system (keyholders).
From a web form, I need to allow my users to change the telephone calling order of the keyholders in the table.
The two important fields are AccountCode nVarChar and CallOrder nVarChar - where AccountCode + CallOrder must be unique.
As an example, the table may contain records with the following data..
1234, 1, Fred
1234, 2, Bert
1234, 3, Bob
If the user wants to make Bob the number 1 keyholder, Fred number 2 and Bert number 3 - what is the best practice for me to approach the update ?
Is this a job for ADO.Net or T-SQL ?
Thanks in advance.
Steve.
Hi
I'm new to SQL and have a problem with the following script:-
INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)
VALUES 2311, 19219, 'BRAN', '01/12/2007', 'Jon')
The above script works OK on a one-off basis but....
Basically, organisation_number_2 (i.e. 19219) always stays the same. However, I need to update organisation_number_1 several times (i.e. 2311 will then change to 2312, 2313, 2314 etc.).
Rather than pasting the script several hundred times and changing the organisation_number_1 value each time, is there a quick way to encompass all the organisation_number_1 values in one go?
I've tried e.g. VALUES (2311,2312), 19219, 'BRAN' etc. but this doesn't seem to work.
Thanks for your help.
Jon
I have a large table with many columns that have rows with N/A or null values, which are gotten from a bulk insert.
In order to format the data properly, we need to convert the data from N/A or null to 0, as the original values come from the supplier.
In my code the best way to achieve this is running multiple queries as
update csv_testing set testing5 = 0 where testing5 like'%N/A%'
update csv_testing set testing6 = 0 where testing6 like'%N/A%'
update csv_testing set testing7 = 0 where testing7 like'%N/A%'
update csv_testing set testing9 = 0 where testing9 like'%N/A%'
etc for about 12 columns
is there a better way of doing this ?
Hi guys.........i'm tryign to update 2 tables in one stored procedure. However i'm getting errors. heres the code i have:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[snow_ors_additionalInfoUpdate]
GO
CREATE PROCEDURE dbo.snow_ors_additionalInfoUpdate
@Reference int,
@CanTravel int,
@SEEmployee varchar(100),
@SE_EMPLOYEE_FROM datetime,
@SE_EMPLOYEE_TO datetime,
@WorkHours Varchar(100),
@DrivingLicence varchar(100),
@CriminalConvictions bit,
@CriminalConvictionsDetails1 text,
@CriminalConvictionsDate1 datetime,
@CriminalConvictionsDetails2 text,
@CriminalConvictionsDate2 datetime,
@CriminalConvictionsDetails3 text,
@CriminalConvictionsDate3 datetime,
@VacancyMonitoring Varchar(100),
@VacancyMonitoringDetails Varchar(50),
AS
UPDATE Account
SET CanTravel= @CanTravel,
SEEmployee = @SEEmployee,
WorkHours= @WorkHours,
DrivingLicence = @DrivingLicence,
CriminalConvictions = @CriminalConvictions,
CriminalConvictionsDetails1 = @CriminalConvictionsDetails1,
CriminalConvictionsDate1 = @CriminalConvictionsDate1,
CriminalConvictionsDetails2 = @CriminalConvictionsDetails2,
CriminalConvictionsDate2 = @CriminalConvictionsDate2,
CriminalConvictionsDetails3 = @CriminalConvictionsDetails3,
CriminalConvictionsDate3 = @CriminalConvictionsDate3
WHERE Account.Reference = @Reference
UPDATE Application
SETVacancyMonitoring = @VacancyMonitoring,
VacancyMonitoringDetails = @VacancyMonitoringDetails,
WHERE Application.Reference = @Reference
GO
and here are the errors i'm getting:
Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 19
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 37
Incorrect syntax near the keyword 'WHERE'.
thanks again guys!
I have a table in the one of our dbs that gets updated through out the day. I believe there are quite a bit of columns that can be moved into a separate table to help with some db contention. Is there some way to audit or view which columns are getting updated on a daily basis?
thanks
How does one update the time part of a smalldatetime field...?
2006-11-16 20:12:00 ---> 2006-11-16 16:30:00
2005-06-01 18:19:00 ---> 2005-06-01 16:30:00
I have tried using the datepart but I'm doing something incorrectly.
thanks,
Jonathan
I want to update only time portion of a datetime column as 00:00:00:000
Values are like:
2006-08-28 17:10:10.607
2007-02-10 11:24:12.090
2007-02-10 11:24:14.967
I want to do them like:
2006-08-28 16:10:10.607
2007-02-10 10:24:12.090
2007-02-10 10:24:14.967
update [ALLBD].[dbo].[Terminal]
set [Hour]= '1900-01-01 09:49:00.000'
where ...
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
-Jeremy
___________________________
Geek At Large
I am attempting to write a conversion of our product for Compact Edition; we already provide it based on SQL Server. The database interface uses ADO through a Python-win32com adaptor, and has worked fine so far. (Note: *not* ADO.net, just plain old COM)
Now, a curious thing happens. When inserting new data through a Recordset, everything works fine - except for columns defined as bigint. There are no exceptions thrown, but when you read the columns back they contain nothing but zeroes. Do the same to any other column type - I've tried integer, numeric, float, nvarchar and ntext so far, and they all seem to work just fine. It does not seem to be conversion-related either, since I've tested the exact same data to various column types. And using bigint on regular SQL Server works just fine.
The code involved is quite unspectacular, and simply switching the column types to integer would solve the immediate problem, but causes potential future issues since we normally store internal IDs in bigint columns, and the values may grow quite large.
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
G'day everyone...
I have two tables: Category and Product.
CategoryID is the Foreign Key of Product table.
I want to update those tables at the same time using stored procedure.
It will work like this.
If I update the Display column of Category table to have the value of 0, it will also update any records in the Product table -that has the same CategoryID- to have the value of 0 in its Display column.
I just read something about trigger and bit gives me idea that I might use it for this case, but don't know how to write the SQL.
Can anyone please help me...
Thanks...
I have a web interface where i have listing of several data and check box for inserting data into SQL server 2005 database table,
so I am able to inset data to sql tables using stored procedure. Now the question is i want to update these inserted records(agency approval column inserted as 1) in same table and assign value 1 fot the checked data to column finace approval as 1.
Here is how ia have webclas library where i script for getting the insert parameterspublic void Process_Payment(ref DataTable TableWithPayments, string Payment)
{SqlCommand InsertCommand = new SqlCommand();
SqlConnection AccessDatabase = new SqlConnection(FinanceSourceWrite.ConnectionString);int PaymentID = 0;
AccessDatabase.Open();
InsertCommand.Connection = AccessDatabase;
//DataTable TemporaryTable = new DataTable();
//TemporaryTable = TableWithPayments;SqlTransaction TransactionProcess = null;
SqlParameter InsertParameters;foreach (DataRow DataCommentInfo in TableWithPayments.Rows)
{InsertCommand.CommandText = "InsertPaymentList"; //THIS IS my stored procedureInsertCommand.CommandType = CommandType.StoredProcedure;
TransactionProcess = AccessDatabase.BeginTransaction();
// SET ALL THE VALUES FOR THE PARAMETERSInsertParameters = InsertCommand.Parameters.Add("@JC_ID", SqlDbType.Int);
InsertParameters.Direction = ParameterDirection.Input;InsertParameters.Value = DataCommentInfo["JC_ID"];
InsertParameters = InsertCommand.Parameters.Add("@Payment_Type", SqlDbType.Int);
InsertParameters.Direction = ParameterDirection.Input;InsertParameters.Value = DataCommentInfo["Payment_Type_ID"];
InsertParameters = InsertCommand.Parameters.Add("@Agency_approval", SqlDbType.Int);InsertParameters.Direction = ParameterDirection.Input;
InsertParameters.Value = DataCommentInfo["Agency_approval"];
Now my stored procedure
ALTER PROCEDURE [dbo].[InsertPaymentList](
@JC_ID int ,
@Payment_Type int,
@Payment_Group int,
@AGENCY_ID int,
@Agency_approval int,
@Agency_approval_date datetime,
@Program_ID nvarchar(50),
@Status bit,
@Jobsite_code_ID int,
@Date_Stamp datetime,
@Provider nvarchar(50),
@UserName nvarchar(256),
@Activity_ID int,
@Subproject_ID int,
@Payment_Support_Retention_List_ID int,
@WPR_ID int,
@Placement_ID int,
@Enrollment_ID int,
@Satisfaction_ID int,
@Enrollment_Bonus_ID int,
@Re_Placement_Bonus_ID int
)AS
INSERT INTO Payment_LIST_AIMS
(JC_ID, Payment_Type, Payment_Group, AGENCY_ID, Agency_approval,
Agency_approval_date, Program_ID, status,
Jobsite_code_ID, Provider, UserName, Placement_ACTV_ID, Placement_Sub_ID, Support_Retention_ID, WPR_ID, Placement_ID,Enrollment_ID,Satisfaction_ID,Enrollment_Bonus_ID,Re_Placement_Bonus_ID)
VALUES @JC_ID, @Payment_Type, @Payment_Group, @AGENCY_ID, @Agency_approval,@Agency_approval_date, @Program_ID, @Status,
@Jobsite_code_ID, @Provider, @UserName, @Activity_ID,
@Subproject_ID, @Payment_Support_Retention_List_ID, @WPR_ID, @Placement_ID,@Enrollment_ID,@Satisfaction_ID,@Enrollment_Bonus_ID,@Re_Placement_Bonus_ID)
SELECT CAST(scope_identity() as int)
Here like you see agency approval column in SQL server table gets value assigned as 1 when Agency user clicks the confirm payment button and so all the values as above....
Now another user Finance user process the same records from the web UI and clicks the process payment button at this stage ..i need to update Finance approval column as 1 agains that particular record existing th the SQL table, there are two three coulmc to be updated , Finance approval(this is where i need help) , Finance approval date , and user
Being a newbie please help me whith how i can fix this
Thanks
Santosh
I am trying to update a SQL table using an excel file which has 2 columns FMStyle and FMHSNum.
FMStyle is my link to the SQL table.
Here is what I have for code....
--------------------------------------------------
Update DataTEST.dbo.zzxstylr
SET hs_num = (select FMHSNum FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c: empStyleHSCodesLoad.xls;HDR=YES ', [Sheet1$]))
Where FMStyle = zzxstylr.style
--------------------------------------------------
Everything seems to be ok except for the "Where FMStyle" is giving me a message Invalid Column name on FMStyle. Do I need to qualify FMStyle and if so how.
I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
my example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
here is an example:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67
so I want to set any contact_id_N = 0 where contact_id_N is = 67
so in the end, the table will look like this:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0
is there a way to do it in one statement?
Hi Gnite everyone, i once again need help with a T-SQL syntax for Auto Correction for insert and update when client enters the wrong format, i;e, creating a SSN Data type and a User Defined Procedure that auto corrects input format i;e,
user inserts into table authors of the pubs DB 525-477845 column au_id that executes auto correction so user doesn't have to put in dashes for SSN format. Please help me with this syntax .
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
Thanks in advance,
Dimeji
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008
I have a question in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
Here is a requirement. Need to update the columns in the tables with the latest values available in CSV.
The file is based on department so the list of tables which is under this department all the corresponding tables needs to updated.
The CSV file which is dynamic in nature the number of columns changes it has header of the columns that needs to be updated.
The destination tables are listed under department table for each department. So I have to update the columns in the tables with the values in csv.
how can i retrieve two columns from sqltable with - seperating the results and a common column name
someth like this
select id ,name from user
id name
1 a
2 b
3 c
i need the result set to be
my_reports
1-a
2-b
3-c
is that possible, if so, could anyone tell me how to do?
Hi please help me out !!
I have to update the incidents table.
The initiator and initdept are two corresponding columns in incidents table 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 Output 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
In order to allow my customers to use their existing data and install updated application, I want to be able to add columns to existing CE tables at startup time, and allow the user to enter data into the new column(s) immediately. I used the ALTER TABLE command as shown in the example below:
cmd.CommandText = "ALTER TABLE tblStores ADD dtDemoDate datetime, dtDemoMode int"
cmdexe = cmd.ExecuteNonQuery()
To add the column, But this does not set the columns in the attached dataset. The program sees the columns and data is able to be added, but is not then in the underlying table? What else do I need to do?
Thank you - b2bMan
I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.
The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).
When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.
I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.
I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:
Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.
Does anyone know how to resolve this issue?
Regards
JLF
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
HI,
I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".
NOW,
HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.
HERE IS HOW THE PROC WRITTENED FOR IT GOES :-
DROP PROCEDURE DE_NAT
CREATE PROCEDURE DE_NAT
AS
BEGIN
DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
SET @MON = MONTH(GETDATE())
SET @YEAR = YEAR(GETDATE())
SET @MONYEAR = @MON + @YEAR
EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
INTO TSD_' + @MONYEAR
+ ' from TSD_20 A
LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
ON A.CUSTOMERS = B.CUSTOMER')
END
THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.
BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'
FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-
CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
----------- ---------- ---------- ---------- ----------- ----------
101023 <NULL> <NULL> <NULL> <NULL> 1.0
102022 1.0 1.62.3 3.4 4.5
NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"
CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?
AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
AGAIN CAUSE A PROBLEM .
PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.
I have three different columns, hour(s), min(s), sec(s)
I can add it up, but will like to convert it into. hrs, mins and sec.
this is how, i am adding it up into seconds.
SELECT ((TotalTimeSpentHrs*60*60)+(TotalTimeSpentMin*60)+(TotalTimeSpentSec))AS totaltime
FROM EST1
How can I convert the total seconds, so that i can input the result in a new column.
Hi all
ALTER TABLE <Table_Name> ALTER COLUMN <column_name> <Data_type>
above query alters the sigle column but how to alter multiple columns at time
saivyshnav
Junior DBA
adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39
I would like to combine adate with atime to get adatetime
I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.
I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.
I have following query which does not produce desired result
SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)
The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.
I'm pretty sure it's simple solution but can not figure out myself.
Hi all!
If I have some mdx I'm using in reporting services like this:
select
{ [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6] } on columns,
{A_list_of_measures } on rows
from ACD_Calls
The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.
I'll post this in reporting services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.
What do you think? Would a matrix be more flexible in this case?
Richard