T-SQL (SS2K8) :: Update A Field In 3rd Table From 2 Joined Tables
Jul 29, 2015
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
FOR XML PATH('')),1,1,'')
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
I'm managing an amature online university and I've been charged with creating a deans list. I have a table for exam results for each course.. currently totaling 5. I have an employeeID column and a total_points column in each table. Sooooo I need to join all the tables and get an average for total_points where the employeeID matches across tables. I have no idea how to write this select.. any help?
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount, where I want purchasedamount to be the sum of the `amount` for THAT item, based on giftregistrypurchases.itemid=giftregistryitems.id:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here's my table definition and data:
USE [tt] GO /****** Object: Table [dbo].[giftregistry] Script Date: 09-05-15 11:15:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistry]( [id] [int] IDENTITY(1,1) NOT NULL,
Each row of my datagrid comes from two tables, A and B, which are (left) joined: not every row from table A has a corresponding row in table B. I think this is quite a common scenario.If I want to edit a row in my datagrid which contains data from both Table A and Table B then presumably I can just use an UPDATE statement behind the scenes.But what happens if I want to edit a particular row in the datagrid which contains data from Table A but no corresponding data from table B? I can't use an UPDATE statement because the record in Table B doesn't yet exist. So what do I do?Does anyone know the answer to this, or could you point me to a good tutorial please?
I have a query written that filters on joined table data. The SELECT looks like this:
SELECT * FROM tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13') ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id SET accrueamt='1348' WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13')
I want to update the startdate column (for all rows) so that when period is 0 then the new value is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the enddate column for the row of the previous column (with the same freq)
ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq
create table #periods (period int , startdate datetime , [enddate] datetime , freq int) insert #periods ( period , startdate , enddate , freq) select 0 , '01-Jan-1900' , '31-Jan-2001' , 1 union all select 1 , '01-Jan-1900' , '28-Feb-2001' , 1 union all select 2 , '01-Jan-1900' , '31-Mar-2001' , 1 union all select 3 , '01-Jan-1900' , '30-Apr-2001' , 1 union all select 4 , '01-Jan-1900' , '31-May-2001' , 1 union all select 0 , '01-Jan-1900' , '31-Jan-2002' , 3 union all select 1 , '01-Jan-1900' , '28-Feb-2002' , 3 union all select 2 , '01-Jan-1900' , '31-Mar-2002' , 3 union all select 3 , '01-Jan-1900' , '30-Apr-2002' , 3 union all select 4 , '01-Jan-1900' , '31-May-2002' , 3
/* I know I need a case statement to test for column 0 and to join the table on itself and have put something together but it fails for column 0 and updates to NULL - I think it must be to do with the join ??
This is what I've got so far :
UPDATE PA1 SET PA1.Startdate = CASE WHEN PA2.period = 0 THEN 2000-12-01 00:00:00.000 ELSE PA1.Enddate END FROM #periods AS PA1 JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1
Hi i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data some people says you can update joined tables using instead of triggers how is that ?is there any example ?
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link, 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts JOIN tblProduct p ON ts.ProductID = p.ProductID JOIN tblTool t ON ts.ToolID = t.ToolID JOIN tblOperation o ON ts.OperationID = o.OperationID WHERE ts.ToolID=66 ') oq LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field: Rec1: Sally Morgan 201-555-1212 Rec2: 555-4040 John Smith Rec3: Jane Houstin 201-555-6452 ext1223
Desired result: Rec1: Sally Morgan 999-999-9999 Rec2: 999-9999 John Smith Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
Table A - data example Idno Entity Flag 1 1 y 2 1 n 3 1 y 4 1 y
Table B - data example Idno Entity 1 1 2 1 3 1
Table C - data example Idno Entity 4 1
I want to update the value flag in table A.Update flag from n to y if idno A in table B and and idno A not in table C, so from table A above, first record and 3rd record remain unchange but 2nd record, flag will be changed from n to y. 4th record where idno A not in table b and in Table C so flag will change from y to n. is it possible, or do i need to use the "fetch" method to compare record by record.
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
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....
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1 a.ID, a.WorkOrder, a.MachineNo, a.Operator, a.PriorInsert,
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate] /* Input Parameters */ @TableName Varchar(20), @Dist Varchar(20) AS Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000)
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and Asset
SELECT DISTINCT EsnId, EsnNumber FROM dbo.ESNTracking WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
I have a db1.tPersonJobHist where I have two columns
[WorkflowCoordinatorFlag] [dbo].[shrsFlag] NOT NULL, [HRBusinessPartnerCode] [dbo].[shrsCode] NULL,
Our developer mistakenly made HRbusinesspartnercode field as nullable. He now wants to change it to Not Nullable.
So there were 4 rows where the values were not null(not sure how). We edited those rows and changed those values to NUll. Now we have no Nulls in that column.
So we brought up the table designer and made the change but got the following error
'tPersonJobHist' table - Unable to modify table.
Cannot insert the value NULL into column 'HRBusinessPartnerCode', table 'BD6578.dbo.Tmp_tPersonJobHist'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Question is why wouldn't it let me alter the table design. and what is tmp_tpersonjobhist.?
I have two tables: eg. a person-table (no nulls allowed), with an idand so on, and a person_course table (an intermediate table in amany-to many relationship between person table and courses tables),with two fields person_id and course_id.But I want to make ONE multipurpose stored procedure, which has ONLYoptional parameters on all fields in the person table AND the fieldcourse_id in the person_course table.I have no problems making optional parameters on the person table (eg.P.ID=ISNULL(@PersonID, P.ID ) ) BUT the problem is, when I try to addan optional parameter on the field course_id it dosn't produce theright results. Some times the course_id is null (because some personshavn't joined a class yet).Is there a way around it?Ole S. Pedersen
DECLARE @id VARCHAR(10) DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT [ServersList] AS 'ID' FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
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.
with cte as ( select DataID, Name, SubType, FileType, MimeType, VersionID, Version
from dtree A1, dversdata A2 where A1.dataid=A2.Docid And A1.Subtype='144' AND A2.mimetype='application/news-message-id' update cte set MimeType = 'application/x-outlook-msg', Subtype=749
what I want to do is to update two columns. Both are from different tables and I get an error..
Hello all, Iv been making a lot of progress on my first functional webapp, but I cannot get this bit of code to work correctly. I think my UPDATE SQL statement is where the problem is. It works fine the first time through when there is no Session("estimateid") set, but after that is set it gives me error this on line 40: Incorrect syntax near '('. 1 Dim CustID As Integer 2 3 Dim DbConnection As SqlConnection 4 DbConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("harringtonairdb").ConnectionString) 5 DbConnection.Open() 6 Dim DbCommand As SqlCommand 7 8 If Session("estimateid") = 0 Then 9 Dim DbSqlInsert As String 10 DbSqlInsert = "INSERT INTO tblcustomers (strname, strAddress1, strAddress2, strCity, strState, strZip, strPhone, strEmail, strContact) VALUES (@name, @address1, @address2, @city, @state, @zip, @phone, @email, @contact)" & "SELECT @@IDENTITY AS CustID" 11 DbCommand = New SqlCommand(DbSqlInsert, DbConnection) 12 Else 13 Dim DbSqlUpdate As String 14 DbSqlUpdate = "UPDATE tblcustomers SET (strcustname=@name, straddress1=@address1, straddress2=@address2, strcity=@city, strstate=@state, strzip=@zip, strphone=@phone, stremail=@email, strcontact=@contact) JOIN tblestimates ON pkcustomerid WHERE pkestimateid=@estimateid" 15 DbCommand = New SqlCommand(DbSqlUpdate, DbConnection) 16 DbCommand.Parameters.AddWithValue("@estimateid", Session("estimateid")) 17 18 End If 19 20 DbCommand.Parameters.AddWithValue("@name", txtCustName.Text) 21 DbCommand.Parameters.AddWithValue("@address1", txtCustAddress1.Text) 22 DbCommand.Parameters.AddWithValue("@address2", txtCustAddress2.Text) 23 DbCommand.Parameters.AddWithValue("@city", txtCustCity.Text) 24 DbCommand.Parameters.AddWithValue("@state", txtCustState.Text) 25 DbCommand.Parameters.AddWithValue("@zip", txtCustZip.Text) 26 DbCommand.Parameters.AddWithValue("@phone", txtCustPhone.Text) 27 DbCommand.Parameters.AddWithValue("@email", txtCustEmail.Text) 28 DbCommand.Parameters.AddWithValue("@contact", txtCustTimes.Text) 29 30 31 If Session("estimateid") = 0 Then 32 CustID = Convert.ToInt32(DbCommand.ExecuteScalar()) 33 DbCommand.Dispose() 34 Dim DbSqlInsert As String 35 DbSqlInsert = "INSERT INTO tblestimates (fkcustomerid) VALUES (@customerid)" & "SELECT @@IDENTITY AS EstimateID" 36 DbCommand = New SqlCommand(DbSqlInsert, DbConnection) 37 DbCommand.Parameters.AddWithValue("@customerid", CustID) 38 Session.Add("estimateid", Convert.ToInt32(DbCommand.ExecuteScalar())) 39 Else 40 DbCommand.ExecuteNonQuery() 41 End If 42 43 DbConnection.Close() 44 DbCommand.Dispose()
I posted Wednesday thinking a SELECT Distinct would solve my problem but it didn't. I have a stored procedure that is used to grab data from 4 tables that I need to join. The 1st table (Application) holds a job applicant's name and some other data The 2nd table (Jobs) holds the Job name and test type The 3rd table (Locations) holds the locations Then there is a foreign key many to many table (Application_Locations) that holds the applicants UserID and a LocationID. This table may have multiple rows for the same applicant with different locations in each row.
When the procedure is ran I want all the data that I am requesting from the Application table, and all the data that I am requesting from the Jobs table but only the 1st returned result of the Join on the Locations and Application_Locations table. What do I need to do to correct this so that I only display 1 row for each UserID no matter how many locations thay may have applied to. (You will notice that there are some IF statements so only the 2nd and 4th queries in the sproc are the ones that apply )
Here is the SPROC that is currently in place but is displaying a row for each location.
IF @JobID <> 9999 BEGIN IF @LocationID <> 9999 BEGIN SELECT A.UserID, A.Completed, A.FolderID, A.AppDateTimeStart, A.ResumeFileName, A.FirstName, A.LastName, A.PrescreenScore, A.JobID, A.ViewPre, A.ViewApp, A.ViewReport, A.ViewResume, J.JobTitle, J.TestType, L.BranchAbbreviation, AL.LocationID FROM Locations L INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID INNER JOIN Application A ON AL.UserID = A.UserID INNER JOIN Jobs J ON J.JobID = A.JobID WHERE AL.LocationID= @LocationID AND A.FolderID= @FolderID AND A.JobID = @JobID ORDER BY CASE WHEN @SortOrder = '4' THEN A.AppDateTimeStart END DESC, CASE WHEN @SortOrder = '6' THEN A.PreScreenScore END DESC, CASE WHEN @SortOrder = '2' THEN A.LastName END DESC, CASE WHEN @SortOrder = '5' THEN A.PreScreenScore END ASC, CASE WHEN @SortOrder = '3' THEN A.AppDateTimeStart END ASC, CASE WHEN @SortOrder = '1' THEN A.LastName END ASC END
ELSE BEGIN SELECT A.UserID, A.Completed, A.FolderID, A.AppDateTimeStart, A.ResumeFileName, A.FirstName, A.LastName, A.PrescreenScore, A.JobID, A.ViewPre, A.ViewApp, A.ViewReport, A.ViewResume, J.JobTitle, J.TestType, L.BranchAbbreviation, AL.LocationID FROM Locations L INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID INNER JOIN Application A ON AL.UserID = A.UserID INNER JOIN Jobs J ON J.JobID = A.JobID WHERE A.FolderID= @FolderID AND A.JobID = @JobID ORDER BY CASE WHEN @SortOrder = '4' THEN A.AppDateTimeStart END DESC, CASE WHEN @SortOrder = '6' THEN A.PreScreenScore END DESC, CASE WHEN @SortOrder = '2' THEN A.LastName END DESC, CASE WHEN @SortOrder = '5' THEN A.PreScreenScore END ASC, CASE WHEN @SortOrder = '3' THEN A.AppDateTimeStart END ASC, CASE WHEN @SortOrder = '1' THEN A.LastName END ASC END END
ELSE BEGIN IF @LocationID <> 9999 BEGIN SELECT A.UserID, A.Completed, A.FolderID, A.AppDateTimeStart, A.ResumeFileName, A.FirstName, A.LastName, A.PrescreenScore, A.JobID, A.ViewPre, A.ViewApp, A.ViewReport, A.ViewResume, J.JobTitle, J.TestType, L.BranchAbbreviation, AL.LocationID FROM Locations L INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID INNER JOIN Application A ON AL.UserID = A.UserID INNER JOIN Jobs J ON J.JobID = A.JobID WHERE AL.LocationID= @LocationID AND A.FolderID= @FolderID ORDER BY CASE WHEN @SortOrder = '4' THEN A.AppDateTimeStart END DESC, CASE WHEN @SortOrder = '6' THEN A.PreScreenScore END DESC, CASE WHEN @SortOrder = '2' THEN A.LastName END DESC, CASE WHEN @SortOrder = '5' THEN A.PreScreenScore END ASC, CASE WHEN @SortOrder = '3' THEN A.AppDateTimeStart END ASC, CASE WHEN @SortOrder = '1' THEN A.LastName END ASC END
ELSE BEGIN SELECT A.UserID, A.Completed, A.FolderID, A.AppDateTimeStart, A.ResumeFileName, A.FirstName, A.LastName, A.PrescreenScore, A.JobID, A.ViewPre, A.ViewApp, A.ViewReport, A.ViewResume, J.JobTitle, J.TestType, L.BranchAbbreviation, AL.LocationID FROM Locations L INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID INNER JOIN Application A ON AL.UserID = A.UserID INNER JOIN Jobs J ON J.JobID = A.JobID WHERE A.FolderID= @FolderID ORDER BY CASE WHEN @SortOrder = '4' THEN A.AppDateTimeStart END DESC, CASE WHEN @SortOrder = '6' THEN A.PreScreenScore END DESC, CASE WHEN @SortOrder = '2' THEN A.LastName END DESC, CASE WHEN @SortOrder = '5' THEN A.PreScreenScore END ASC, CASE WHEN @SortOrder = '3' THEN A.AppDateTimeStart END ASC, CASE WHEN @SortOrder = '1' THEN A.LastName END ASC END END GO
I am using SQL Server 2005 to publish joined tables for SQL Mobile subscribers for merge replication and column level tracking.
Using Management Studio I am trying to join tables and specify row filters on the joined tables. I.E. table 1 is joined with table 2. I need to define row filters for table 1 and row filters specific to table 2.
An example would be: Table 1 is a customer table that I filter on a specific customer. Table 2 might be an orders table that I need to join to get the customers orders but I also want to filter for open orders only.
When I specify the row filter for table 2 the join appears to be ignored and I receive the complete table 2 with the row filter applied.
I have searched the online books and the web and I have not run accross an example of using both joins and row filters where the filters are specified for both joined tables.
