Update Based on Value from Another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Update Based on Value from Another Table

Author

Topic

MCusuma1
Starting Member

13 Posts

Posted - 2013-03-06 : 13:32:00

I need to create a query that will set an Autonumber on a selection based on a value from another table.

Basically what I am doing is assigning a sequence of inspections to a technician in a table (CPTestPointInspection) based on a table containing the technician's ID codes (ASSIGNED_TECHS).

So, there would be two loops: one going through the technician table and getting the ID's from the TECH_ID field, and one going through the inspection table assigning the inspections from 1 to n based on a WHERE clause stating the ASSIGNED_USER field = TECH_ID field from the technician table. Once all active inspections for that technician have been assigned, it would move on the the next technician code in the tech table and begin again at 1.

I have this done in ArcPy using a searchcursor and updatecursor(this is done on an esri SDE table) but it takes far too long and I would like to re-work it for SQL so it would run much quicker. I have been trying for hours with no luck, any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 13:38:10

sounds like this to me


INSERT CPTestPointInspection
SELECT t.TECH_ID,i.Inspection
FROM ASSIGNED_TECHS t
INNER JOIN Inspections i
ON i.ASSIGNED_USER  = t.TECH_ID

i've assumed column names so make sure you use correct columns names in your query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MCusuma1
Starting Member

13 Posts

Posted - 2013-03-06 : 14:14:00

ASSIGNED_USER is a field in CPTestPointInspection that is used to join to the ASSIGNED_TECHS table. The ASSIGNED_TECHS table is created just prior to this query where it pulls all current techs out of CPTestPointInspection (that part is done).

So let's say there are two techs in the ASSIGNED_TECHS Table, Tech1 and Tech2:

If there are 31 pending inspections for Tech1 in CPTestPointInspection, (based on WHERE CPTestPointInspection.ASSIGNED_USER = ASSIGNED_TECHS.Tech1) it would assign a value of 1 to 31 in a field called SEQ_NO for Tech1 in CPTestPointInspection (sorted in ascending order by structure number).

Likewise, if there are 52 pending inspections for Tech2 (based on WHERE CPTestPointInspection.ASSIGNED_USER = ASSIGNED_TECHS.Tech2) it would assign values of 1 to 52 in SEQ_NO for Tech2 sorted by Structure Number.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 23:21:42

for generatinmg sequence number you need this


SELECT t.TECH_ID,i.Inspection,ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.Inspection) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN Inspections i
ON i.ASSIGNED_USER  = t.TECH_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MCusuma1
Starting Member

13 Posts

Posted - 2013-03-07 : 12:28:14

Thank you, this worked well. I am now trying to implement this into an update query. I have:

UPDATE CPTESTPOINTINSPECTION_SYSTM
SET SEQ_NO = ROW_NUMBER() OVER (PARTITION BY ASSIGNED_TECHS.TECH_ID ORDER BY CPTESTPOINTINSPECTION_SYSTM.GAS_CORR_STRUCT_NO)
FROM CPTESTPOINTINSPECTION_SYSTM
INNER JOIN ASSIGNED_TECHS
ON CPTESTPOINTINSPECTION_SYSTM.ASSIGNED_USER = ASSIGNED_TECHS.TECH_ID

However this is telling me that

"Msg 4108, Level 15, State 1, Line 4
Windowed functions can only appear in the SELECT or ORDER BY clauses."

MCusuma1
Starting Member

13 Posts

Posted - 2013-03-07 : 14:00:49

Never mind I got it:

UPDATE CPTESTPOINTINSPECTION_SYSTM
SET CPTESTPOINTINSPECTION_SYSTM.SEQ_NO = seq

FROM
(
SELECT t.TECH_ID,i.GAS_CORR_STRUCT_NO, INSPECTIONDATE, SEQ_NO, ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.GAS_CORR_STRUCT_NO) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN CPTESTPOINTINSPECTION_SYSTM i
ON i.ASSIGNED_USER = t.TECH_ID
) CPTESTPOINTINSPECTION_SYSTM
WHERE CPTESTPOINTINSPECTION_SYSTM.INSPECTIONDATE IS NULL

MCusuma1
Starting Member

13 Posts

Posted - 2013-04-12 : 10:28:12

Hi, this is still not working correctly...it is not populating the table from 1 to n based on the number of structures per assigned tech, it is populating them based on some row number equivalent. I am now trying to do it with cursors, so far I have:


DECLARE @id VARCHAR(10)
DECLARE @stop int
SET @STOP = 0

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [TECH_ID] AS 'ID' 
    FROM [dbo].[Assigned_Techs]
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @id
		
		UPDATE CPTESTPOINTINSPECTION_RECHECK
		SET STOP = @STOP + 1
		SET SEQ_NO
		ORDER BY GAS_CORR_STRUCT_NUM
		WHERE CURRENT of myCursor



    FETCH NEXT FROM myCursor INTO @id

END

CLOSE myCursor
DEALLOCATE myCursor

But this is not working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 12:13:20

it should be


UPDATE CPTESTPOINTINSPECTION_SYSTM
SET CPTESTPOINTINSPECTION_SYSTM.SEQ_NO = seq

FROM 
(
SELECT t.TECH_ID,i.GAS_CORR_STRUCT_NO, INSPECTIONDATE, SEQ_NO, ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.GAS_CORR_STRUCT_NO) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN CPTESTPOINTINSPECTION_SYSTM i
ON i.ASSIGNED_USER = t.TECH_ID
WHERE CPTESTPOINTINSPECTION_SYSTM.INSPECTIONDATE IS NULL
) CPTESTPOINTINSPECTION_SYSTM

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MCusuma1
Starting Member

13 Posts

Posted - 2013-04-15 : 08:14:19

I tried this and it gave me

"Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "CPTESTPOINTINSPECTION_SYSTM.INSPECTIONDATE" could not be bound."

MCusuma1
Starting Member

13 Posts

Posted - 2013-04-15 : 08:41:33

I got it, was an easy fix. Had to add an "is not null" for structure number to the where clause and get rid of the pretext for inspectiondate:

USE psegGDB
UPDATE CPTESTPOINTINSPECTION_SYSTM
SET CPTESTPOINTINSPECTION_SYSTM.SEQ_NO = seq

FROM
(
SELECT t.TECH_ID,i.GAS_CORR_STRUCT_NO, INSPECTIONDATE, SEQ_NO, ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.GAS_CORR_STRUCT_NO) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN CPTESTPOINTINSPECTION_SYSTM i
ON i.ASSIGNED_USER = t.TECH_ID
WHERE INSPECTIONDATE IS NULL
AND GAS_CORR_STRUCT_NO IS NOT NULL
) CPTESTPOINTINSPECTION_SYSTM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 12:28:42

cool
glad that you got it sorted out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources