Running An UPDATE Statement Only If A Column Exists
Nov 1, 2007
I'm trying to write a script that would only update a column if it exists.
This is what I tried first:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
UPDATE dbo.Enrollment SET nosuchfield='666'
END
And got the following error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'nosuchfield'.
I'm curious why MS-SQL would do syntax checking in this case. I've used this type of check with ALTER TABLE ADD COLUMN commands before and it worked perfectly fine.
The only way I can think of to get around this is with:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
declare @sql nvarchar(100)
SET @sql = N'UPDATE dbo.Enrollment SET nosuchfield=''666'''
execute sp_executesql @sql
END
which looks a bit awkward. Is there a better way to accomplish this?
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
In MS Access I can do in one SQL statement a update if exists else ainsert.Assuming my source staging table is called - SOURCE and my targettable is called - DEST and both of them have the same structure asfollowsKeycolumns==========MaterialCustomerYearNonKeyColumns=============SalesIn Access I can do a update if the record exists else do a insert inone update SQL statement as follows:UPDATE DEST SET DEST.SALES = SOURCE.SALESfrom DEST RIGHT OUTER JOIN SOURCEON (DEST.MATERIAL = SOURCE.MATERIAL ANDDEST.CUSTOMER = SOURCE.CUSTOMER ANDDEST.YEAR = SOURCE.YEAR)This query will add a record in SOURCE into DEST if that record doesnot exist in DEST else it does a update. This query however does notwork on SQL 2000Am I missing something please share your views how I can do this inSQL 2000.ThanksKaren
View 6 Replies
View Related
May 1, 2015
SQL Ver: 2008 (not r2)
Problem: The following code returns correct results when moving variable declarations and update statement outside a stored procedure, but fails to return a value other than zero for the "COMPANY TOTAL" records. The "DEPT TOTAL" result works fine both in and outside the sp.This may have to do with handling NULL values since I was getting warning message earlier involving a value being eliminated by an aggregate function involving a NULL. I only got this message when running inside the sp, not when running standalone. I wrapped the values inside the SUM functions with an ISNULL, and now return a zero rather than NULL for the "COMPANY TOTAL" records when running inside SP.All variable values are correct when running.
SQL CODE:
DECLAREÂ
    @WIPMonthCurrent date = (SELECT TOP 1 WIPMonth FROM budxcWIPMonths WHERE ActiveWIPPeriod = 'Y')
  select @WIPMonthCurrent as WIPMonthCurrent
 Â
[code]....
View 10 Replies
View Related
Aug 25, 2006
Hi,
I'm new to SQL Server but not new to SQL because I used it with Oracle. I wonder if it is possible to do this kind of statement on SQL Server:
UPDATE TableX M
SET (M.column1, M.column2, M.column3)=
(SELECT T.column1, T.column2, G.column3)
FROM Table_Y T,
Table_Z G
WHERE join condition))
WHERE EXISTS (join condition for TableX)
Basically, I'd like to update multiple columns in one statement but for some reason I can not get it to work.
What would be the equivalent on SQL Server?
Thanks for the help,
Laszlo M
View 4 Replies
View Related
May 17, 2008
Is there a way to set a field value to the column default in an update statement?
Eg.
UPDATE Table2 SET field1 = DefaultValue
where DefaultValue is the field1 column default in the table definition.
The reason I need to do this is when I delete a record from Table1, I need to set the foreign key in Table2 to the default (I don't want to delete the record in Table2, just want to set the key to a default key). I could hard-code the default value in the stored procedure but I think that's just not clean. If I create a new instance of the DB and the default value changes, I'd need to change the stored procedure(s). Just not clean...
To avoid a drawn-out discussion, there are reasons why I can't setup a relationship between the two tables and use "ON DELETE SET DEFAULT".
Any info greatly appreciated.
View 9 Replies
View Related
Nov 14, 2013
i have a table named masterlist wherein the columns are :
name-----age------sex
andrew---19-------male
trisha---23------female
and i have also another table which is namelist that is linked to the masterlist table.. after i search for the record andrew in the table namelist..i updated andrew as 25 and sex is female..now i want reset andrew's record, same to the records that andrew has in the table masterlist..
View 3 Replies
View Related
Sep 9, 2015
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.
Now, I was asked to log the specific record where the failure was occured.
Something like log the identity column value or primary key value which record has failed.
View 4 Replies
View Related
Jul 23, 2005
I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0
View 8 Replies
View Related
Jan 12, 2007
Set publisher database table column to 1.
Replicate table to subscriber (column is also 1 on subscriber once it is replicated).
Set subscriber database column to 2.
Update publisher database column to 1 (even though it is already 1).
Change is NOT replicated to subscriber (it stays at 2).
This means that replication is comparing the insert statement to its current value in the database.
Is there any way to change this behavior? We want the the column to replicate to the subscriber.
View 9 Replies
View Related
Sep 9, 2015
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.Now, I was asked to log the specific record where the failure was occured.Something like log the identity column value or primary key value which record has failed.
View 2 Replies
View Related
Jun 14, 2007
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies
View Related
Feb 29, 2008
Hi all,
I have the following SQL script that works fine, but I like to view all the fields for the records that are not exists in “capdb.dbo.abc “ for “capdb2.dbo.abc� table instead of some fields :
select distinct cp2abc.subj_num , cp2abc.abc_age, cp2abc.ABC_LETHARGY, cp2abc.ABC_STEREOTYPY
, cp2abc.ABC_STEREOTYPY, cp2abc.ABC_HYPERACTIVITY, cp2abc.ABC_INAPPROPRIATE_SPEECH
from capdb2.dbo.abc as cp2abc, capdb.dbo.abc as cp1abc
where not exists
(select cp1abc.subj_num, cp1abc.abc_date from capdb.dbo.abc as cp1abc where cp2abc.subj_num = cp1abc.subj_num
and cp2abc.abc_DATE = cp1abc.abc_date)
I tried cp2abc.*, but the returns entire the records in the tables.
select cp2abc.*
from capdb2.dbo.abc as cp2abc, capdb.dbo.abc as cp1abc
where not exists
(select cp1abc.subj_num from capdb.dbo.abc as cp1abc where cp2abc.subj_num = cp1abc.subj_num
and cp2abc.abc_DATE = cp1abc.abc_date)
Is there a way to accomplish this
Thanks for any help.
Regards,
Abrahim
(moved from Script Library by Jeff)
View 1 Replies
View Related
Sep 11, 2007
Posted - 09/10/2007 : 15:53:26
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
thanks in advance!
jm
View 1 Replies
View Related
Jul 25, 2015
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
View 2 Replies
View Related
Apr 5, 2007
I'm trying to gather some user statistics based on 3 conditions. First I want to check if the referring querystring is already in the database. If not insert it into the db. Second, if the querystring is already in the database, then check if the ip-address of the user is already in the database. If it is, then check if the ip address was inserted today. If not, update the "refCountIn" field with +1. The problem lies in the third condition where we check if the ip-address was inserted today and if false, update the "refCountIn" field with +1 and if true, do nothing.Below is the code I have until now: 1 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
2
3 ' *** Declare the variables
4 Dim getStatCmd As String
5 Dim updStatCmd As String
6
7 Dim myRef As String = Request.QueryString("ref")
8 Dim myQueryString As String = Request.ServerVariables("QUERY_STRING").Replace("ref=", "")
9 Dim myRemoteAddr As String = Request.ServerVariables("REMOTE_ADDR")
10 Dim myHttpReferer As String = Request.ServerVariables("HTTP_REFERER")
11
12 Dim dtNow As Date = DateTime.Now
13 Dim dtToday As Date = DateTime.Today
14
15 ' *** Conditional INSERT command
16 getStatCmd = _
17 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime = '" & dtToday & "') " & _
18 "BEGIN " & _
19 "SELECT 'This clickin has already been recorded!'" & _
20 "END ELSE BEGIN " & _
21 "SELECT 'Clickin recorded' " & _
22 "INSERT INTO tblReferers(robotName, refIpAddress, refReferer, refTime) " & _
23 "VALUES(" & _
24 "'" + myQueryString + "'," & _
25 "'" + myRemoteAddr + "'," & _
26 "'" + myHttpReferer + "'," & _
27 "'" + dtToday + "')" & _
28 "END "
29
30
31 ' *** Conditional UPDATE command
32 updStatCmd = _
33 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime <> '" & dtToday & "') " & _
34 "UPDATE tblReferers " & _
35 "SET refCountIn = refCountIn + 1, refTime = '" & dtNow & "' " & _
36 "WHERE refIpAddress = '" & myRemoteAddr & "' AND robotName = '" & myRef & "'"
37
38 Dim insConnCmd As New SqlCommand(getStatCmd, New SqlConnection(connSD))
39 Dim updConnCmd As New SqlCommand(updStatCmd, New SqlConnection(connSD))
40
41 insConnCmd.Connection.Open()
42 insConnCmd.ExecuteNonQuery()
43 insConnCmd.Connection.Close()
44
45 updConnCmd.Connection.Open()
46 updConnCmd.ExecuteNonQuery()
47 updConnCmd.Connection.Close()
48
49 End Sub
Anyone with an idea on how to solve this one? I think I need to write a subquery for the third condition, but I don't have a clue on how to handle this.Thanks in advance for your help!
View 2 Replies
View Related
Jun 16, 2004
Can someone give me a hand with this?
Ok I have a table called allstocks, with a primary key of ticker, I also have a todaysstocks table with a primary key of ticker.
Todaysstock table updates the allstocks table with this statement,
insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under]) select exchange,[todays date],ticker,[opened date], [closed date],[over/under] from todaysstocks
this works fine, unless the ticker already exists. How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?
ANy help would be appreciated. Thanks
View 4 Replies
View Related
Feb 28, 2008
Hello,
I would like to set a variable within my if exists statement, however SQL is throwing and error stating:
Incorrect syntax near '='.
If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable
Here is my code block:
Code Snippet
declare @permissionID int;
if exists(select @permissionID = Id from Permission
where [Description] = 'SettlementReport')
Thanks,
Flea#
View 3 Replies
View Related
Jul 20, 2005
Hi,I have 2 tables in an SQLServer db.I want to compare table A with table B and add any records that EXISTin table B but dont exist in table A, to table A.Can anyone help me with the SQL?TIAHitcher
View 1 Replies
View Related
May 28, 2002
Hello.
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
Thanks in advance for any replies.
David.
View 3 Replies
View Related
Feb 20, 2006
Hi @ all
I'm using MSSQL and PHP.
I've got the following sql statement:
$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')
The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused:
mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not.
I tried:
$result = mssql_query($msquery);
$succeed = mssql_rows_affected ($result);
And:
$result = mssql_query($msquery);
$succeed = mssql_num_rows($result);
to get the rows, affected by the statement, but both return:
supplied argument is not a valid MS SQL-Link resource
Anyone an idea?
View 2 Replies
View Related
Aug 23, 2004
I am having trouble finishing my query.
This is what I have:
IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})
I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.
Any suggestions would be appreciated.
Thanks,
Laura
View 13 Replies
View Related
Feb 26, 2008
When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.
I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.
My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.
I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.
View 3 Replies
View Related
Apr 4, 2007
Hello,
it's me again :)
I've got a - what I think - simple question.
There is table A with Col1,Col2,Col3 and Table B with Col1,Col2,Col3
I want all rows from B in A. If a row already exist in A, then update all columns, else just insert the row.
Can someone please help me with a small syntax.
Thank you!
View 6 Replies
View Related
Jan 6, 2004
Im trying to select rows on the following criteria
My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read.
I am able to return the following:
Comment UnReadMy Job 164
Comment UnReadFor Cam 166
Comment ReadThe Job 157
Comment ReadThird 159
Comment ReadMy Job 164
On Site Visit ReadMy newest job for log test 167
but as you can see jobid 164 appears in both groups and I need it to be one or the other
I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql:
CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID
@UserID INT
AS
BEGIN
IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead')
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead'
ORDER BY BackUp_Read ASC
END
ELSE
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'Read'
ORDER BY BackUp_Read ASC
END
END
GO
Thanks again
View 12 Replies
View Related
Feb 18, 2004
Hi,
I have a 'Products' table (with: 'uid' and 'CatName' columns) and 'ProductCategory' table (with: 'uid', 'ProductID', 'CategoryID' columns).
I got stored procedure below to update or insert new row to 'ProductCategory' table whenever 'Products' table has been updated or new products has been added to it.
Update part works just fine but when new row has been added to 'Products' this storedProc dosn't insert it into 'ProductCategory' table, it does that only when 'ProductCategory' table is empty, I'm afraid it's because first column 'uid' in 'ProductCategory' table is an Identity column... I’m not sure how should I go about that problem. This is my stored procedure:
DECLARE @CatNo INT, @CatName varchar(10)
SET @CatNo = 2
SET @CatName = 'bracket'
IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )
BEGIN
UPDATE ProductCategory SET CategoryID = @CatNo
FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid
END
ELSE
BEGIN
INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @CatNo FROM Products
WHERE Products.CatName = @CatName
END
SET @CatNo = 3
SET @CatName = 'cable'
IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )
BEGIN
UPDATE ProductCategory SET CategoryID = @CatNo
FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid
END
ELSE
BEGIN
INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @CatNo FROM Products
WHERE Products.CatName = @CatName
END
(... Goes for another 37 categories)
Thank you for help.
Kooba
View 3 Replies
View Related
Jul 20, 2005
I am trying to determine the next registered session of a student so Ican calculate the number of skipped sessions.Scenario: I have a student registration summary table. One row foreach student and the student's registered session. I want to update agiven row with the next higher registered session (into a field callednext_registered_session_skey if the row exists). I can then use thediff of the skeys to determine how many sessions the student skippedfor each registration period.Example: Student X registers each fall for one session for 4 years.The file might look like:STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTRED_SESSION_SKEY123456789 200201 100null123456789 200301 104null123456789 200401 108null123456789 200501 112nullI need to update the NEXT_REGISTRED_SESSION_SKEY so I end up with:STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTRED_SESSION_SKEY123456789 200201 100104123456789 200301 104108123456789 200401 108112123456789 200501 112nullI can then say SESSIONS_SKIPPED = NEXT_REGISTRED_SESSION_SKEY –SESSION_KEY (logically speaking, not syntactically)This is what I have so far as example:UPDATE F_REGISTRATIONSET NEXT_REGISTERED_SESSION_SKEY = (select top 1 nextr.session_skeyfrom f_registration rinner joinf_registration nextron r.student_skey = nextr.student_skey and nextr.session_skey[color=blue]> r.session_skey[/color]order by r.session_skey desc)WHERE STUDENT_ID = '577665705';SELECT student_skey, student_id, session_id, session_skey,next_registered_session_skey, * FROM F_REGISTRATION WHERE STUDENT_ID= '577665705' order by session_skey descRESULTS:STUDENT_SKEY STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTERED_SESSION_SKEY125137 577665705 200404 309 311125137 577665705 200403 308 311125137 577665705 200402 307 311125137 577665705 199804 285 311125137 577665705 199803 284 311125137 577665705 199802 283 311125137 577665705 199704 281 311TIARob(I restricted with the where = ‘577665705' so I did not have to waitto update all the rows)
View 6 Replies
View Related
Oct 25, 2007
I had implemented as in the link to insert or update
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
What i want to know is... how can i assume there are no duplicate records.
I used Distinct keyword and queried it showed me all are distint but some where i find some duplicates just don't know why i am having when i look at the data both are exactly same...
Please let me know how can i fix it.
Urgent..
Thanks
View 5 Replies
View Related
Dec 17, 2007
Is there a way to structure a query to update an existing table record if it already exists, otherwise insert a new record into that table?
View 2 Replies
View Related
Feb 9, 2007
I've decided to post this as a sticky given the frequency this question is asked.
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
Phil
View 60 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store   Type   Sales
11 Â Â Â Â Â A Â Â Â Â 1000
23 Â Â Â A Â Â Â 1980
23 Â Â Â B Â Â Â 50
5 Â Â Â Â B Â Â Â 560
I want to filter the store in "where clause" Â where
1)- if the store has type A and  B, then assign only A
2)- if the store has  type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= Â (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 Replies
View Related
Nov 30, 2005
Hello
How do you check if a column exist ?
for a table :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO
but I dont find it for a column
Thank you
View 4 Replies
View Related
Jul 30, 2014
I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.The trigger works fine until a day we found out there are same addresses exist in the log table for the same student. so below is what I modified the trigger to. I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:
ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress]
FOR UPDATE
AS
DECLARE @rc AS INT ;
[code]....
View 2 Replies
View Related
Jan 8, 2008
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
View 2 Replies
View Related