Query

[Waqas ali]

dear experts,

i have below table with msg_seqno ,addjobid and job_id. i want to update same job_id against each msg_seqno in AddJobId. how i have to write a query.

is it by correlated query or with simple update query? please write a query.

MSG_SeqNo	AddJobId	job_ID
20160301004811982451		1216
20160301004812593237
20160301004812661445
20160301004812839436
20160301004824828647
20160301004900825884
20160301004921093556
20160301004921326065
20160301004938098232
20160301004939326558
20160301005011097547
20160301005015327326
20160301005025826370
20160301005025866153
20160301005026343194		1216

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[alansidman]

do you want all the records to have the job id of 1216? or something else? If something else, how will it be determined.

 

[Waqas ali]

yes i want all the records have same job id if you see msg_seqNo it is date and time starting year ,month,day -- time with miliseconds

 

[xenou]

Updating all records to the same job id is easy:

Update

Set Job_ID = 1216

 

[Waqas ali]

dear xenou,

this i know and i don't want to put manually , if you see msg_seqno min and max value these values have job id value , it should check min and max msg_seqno and update in between min and max msg_seqno same job id automatically. this one i want to know how i have to write a query?

 

[xenou]

That makes more sense actually. Still this is awkward. This query has one job_ID hardcoded, and it assumes there is a pair of them. It still doesn't seem very useful in practice:

update Table1 t1
set MSG_SeqNo = 1216
where
	MSG_SeqNo > (select min(MSG_SeqNo) from Table1 where job_ID = 1216)
	and 
	MSG_SeqNo < (select max(MSG_SeqNo) from Table1 where job_ID = 1216)

 

[Waqas ali]

ok i give you one example.

what is difference in foreign key and primary key . foreign key has unique record but primary key has multiple record for same foreign key. these all records are details record of 1216 job id.
from where i am getting data they put only job id in details record in min value and max value to make it normalize i add extra column and i want to update job id with in between min and max value. i put one example there are in thousands record with different job id .

i want that query which get unique job id automatically job id and its min and max msg_seqno and than update job id in new column between min and max msg_seqNo.

in above query you put manually but i need it should get automatically unique.

 

[Waqas ali]

sorry to all primary key has unique record and foreign key has multiple records for each primary key.

 

[xenou]

Sorry for the belated answer. To do this it seems best to me to use a "batch script" type of approach so we can load some temp tables and manipulate the date - i.e., to run a series of sql statements in sequence rather than trying to create on single SQL statement that does it all at once.

So what follows is an example of how that could be done using VBA to script a few SQL statements to run in sequence. I've assumed the original table is called "Table6", and also this script requires a few tables be present for use during the execution of the script. In practice, I would create these tables once by hand and just leave them in the DB at all times, ready for use. But you can hide the tables so they don't clutter up your navigation pane since you don't really need to see them. So I have one temp table to get my Min values for MSG_SeqNo, another to get my Max values for MSG_SeqNo, and a third to put it all together and create the records that show what needs to be updated and what to updated values will be.

A sample DB with code and tables can be downloaded HERE.

Sample VBA Code:

Sub UpdateMsg_SeqNo()

'  Assume Table6 is the table to be updated.
'  Also assume three temp tables created:
'    Table6_TEMP01 (job_ID, MSG_SeqNoMin)
'    Table6_TEMP02 (job_ID, MSG_SeqNoMax)
'    Table6_TEMP03 (job_ID, MSG_SeqNo)

Dim s As String

    '//Truncate Temp Tables
    s = "DELETE t.* FROM Table6_TEMP01 t"
    Run_SQL s
    s = "DELETE t.* FROM Table6_TEMP02 t"
    Run_SQL s
    s = "DELETE t.* FROM Table6_TEMP03 t"
    Run_SQL s

    '//List of job_IDs with MinSeqNo
    s = "INSERT INTO Table6_TEMP01"
    s = s & " SELECT job_ID, Min(MSG_SeqNo) AS MSG_SeqNoMin"
    s = s & " FROM Table6"
    s = s & " WHERE Nz(job_ID) <> ''"
    s = s & " GROUP BY job_ID"
    Run_SQL s

    '//List of job_IDs with MaxSeqNo
    s = "INSERT INTO Table6_TEMP02"
    s = s & " SELECT job_ID, Max(MSG_SeqNo) AS MSG_SeqNoMax"
    s = s & " FROM Table6"
    s = s & " WHERE Nz(job_ID) <> ''"
    s = s & " GROUP BY job_ID"
    Run_SQL s
    
    '//List of records with values to be updated
    s = "INSERT INTO Table6_TEMP03"
    s = s & " SELECT A.MSG_SeqNo, B.job_ID"
    s = s & " FROM Table6 A"
    s = s & " INNER Join"
    s = s & " ("
    s = s & " SELECT t1.job_ID, t1.MSG_SeqNoMin, t2.MSG_SeqNoMax"
    s = s & " FROM Table6_TEMP01 AS t1"
    s = s & " INNER JOIN Table6_TEMP02 AS t2"
    s = s & " ON t1.job_ID = t2.job_ID"
    s = s & " ) B"
    s = s & " ON A.MSG_SeqNo > B.MSG_SeqNoMin AND A.MSG_SeqNo < B.MSG_SeqNoMax"
    Run_SQL s

    '//Update records
    s = "UPDATE Table6 t1"
    s = s & " INNER JOIN Table6_TEMP03 t2"
    s = s & " ON t1.MSG_SeqNo = t2.MSG_SeqNo"
    s = s & " SET t1.job_ID = t2.job_ID"
    s = s & " WHERE Nz(t1.job_ID) = ''"
    Run_SQL s

End Sub

Sub Run_SQL(ByRef s As String)

    DoCmd.SetWarnings False
    DoCmd.RunSQL s
    DoCmd.SetWarnings True
    Debug.Print s & vbCrLf '//Comment this out if you don't want to inspect the sql text

End Sub

 

[Waqas ali]

thanks a lot xenou,

i want to understand like below queries either insert, select or update. which you wrote sub query or correlated as below. in bracket query you mention t1 and t2 table alias for the inner bracket query you mention B also in bracket fields are treated as separate tables but outside for B all fields are same for B it can be call like B.anyFieldName how these query works. i want to understand how these queries are work.

'//List of records with values to be updated
s = "INSERT INTO Table6_TEMP03"
s = s & " SELECT A.MSG_SeqNo, B.job_ID"
s = s & " FROM Table6 A"
s = s & " INNER Join"
s = s & " ("
s = s & " SELECT t1.job_ID, t1.MSG_SeqNoMin, t2.MSG_SeqNoMax"
s = s & " FROM Table6_TEMP01 AS t1"
s = s & " INNER JOIN Table6_TEMP02 AS t2"
s = s & " ON t1.job_ID = t2.job_ID"
s = s & " ) B"
s = s & " ON A.MSG_SeqNo > B.MSG_SeqNoMin AND A.MSG_SeqNo < B.MSG_SeqNoMax"
Run_SQL s