Use a SELECT @@IDENTITY as a sub-query in an UPDATE ?

[mark4asp]

Is it possible to use a SELECT @@IDENTITY statement as a sub-query in
an UPDATE ?

For instance I wanted to run the following query but Access would not
allow me to enter it:

I've tried each of the following:

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);

Why is it not possible to do this?

The following does work:

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);

 

[Michel Walsh]

Hi,


You don't need to "select" it. If you use ADO ( not DAO, not the query
designer), the following should work:

CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2 WHERE
f3=@@IDENTITY"


Note that @@IDENTITY is maintained by connection. You have to use the
same connection object that you did something to "assign" the @@IDENTITY to
something, that to get it back. As a trivial example, but worth of mention,
@@IDENTITY of the currentProject.Connection won't be aware of the latest DAO
operations.



Hoping it may help,
Vanderghast, Access MVP

 

[mark4asp]

Thanks,

Are you telling me that I need to keep that specific connection open?
I've included most of my code below (including my ADO execute
function. As you can see the connection is closed each time a single
SQL statement is executed.

Do I need to change this code so that the same connection stays open
during the first to the second SQL statement?

i.e. Do I need to remove the:
conn.Close
Set conn = Nothing
statements so that they are only executed after the 2nd SQL statement,
not after the first?

I'm using the OLEDB Jet 4 driver & ADO in an ASP page. I believe the
connections are normally pooled after closing them.

Apologies for being so pedantic but only a yes/no answer is required
here.

========== ========== ==========
Code
========== ========== ==========
sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
doAdoExec "Blog", sSQL
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID= @@IDENTITY"
doAdoExec "Blog", sSQL

Sub doAdoExec(connection, sqlcmd)
Dim conn, cmd, ConnectionString, ConnectionTimeout,
Dim CommandTimeout, RuntimeUserName, RuntimePassword,
Dim CursorLocation
' ----------------------------------------------------------------
' get connection details from Application vars.
' ----------------------------------------------------------------
ConnectionString = Application(connection & "_ConnectionString")
ConnectionTimeout = Application(connection & "_ConnectionTimeout")
CommandTimeout = Application(connection & "_CommandTimeout")
If Not Application("sspi") Then
RuntimeUserName = Application(connection & "_RuntimeUserName")
RuntimePassword = Application(connection & "_RuntimePassword")
End If
CursorLocation = Application(connection & "_CursorLocation")

' ----------------------------------------------------------------
' Connection Object & Settings
' ----------------------------------------------------------------
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = ConnectionTimeout
conn.CommandTimeout = CommandTimeout
conn.Open ConnectionString, RuntimeUserName, RuntimePassword

' ----------------------------------------------------------------
' Command Object & Settings
' ----------------------------------------------------------------
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = sqlcmd
cmd.Execute

Set cmd = Nothing
conn.Close
Set conn = Nothing
End Sub
========== ========== ==========
End Code
========== ========== ==========

(apoligies for the top post)

 

[mark4asp]

It didn't work.

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID = @@IDENTITY

I get this error when trying to save an Access query:

syntax error (missing operator) in
query expression blog_ID = @@IDENTITY

Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
Integer field in the same table. The Access 2002 database had SQL
Server Compatible Syntax (ANSI 92) checked in Options.

The INSERT statement does worked OK. So there was an @@IDENTITY
created in that session.

========== ========== ==========
ASP Code:
========== ========== ==========

Set conn = getAdoConn("Blog")
sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
doAdoSqlCmd conn, sSQL
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = @@IDENTITY"
doAdoSqlCmd conn, sSQL
KillConn conn

Function getAdoConn(db)
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout = 30
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
Server.MapPath("..\database\" & db & ".mdb;") &_
"User ID=Admin;Password=;"
Set getAdoConn = conn
End Function

Sub doAdoSqlCmd(conn, sqlcmd)
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = sqlcmd
cmd.Execute
Set cmd = Nothing
End Sub

Sub KillConn(cn)
cn.Close
Set cn = Nothing
End Sub

========== ========== ==========
End Code.
========== ========== ==========

 

[Bob Barrows]

I would think you would have better success doing this* :

sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
doAdoSqlCmd conn, sSQL
sSQL = "Select @@IDENTITY"
set rs = conn.execute sSQL,,1
newID = rs(0)
rs.close
set rs=nothing
response.write newID 'for debugging
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = " & newID
doAdoSqlCmd conn, sSQL
KillConn conn

Sub doAdoSqlCmd(conn, sqlcmd)
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = sqlcmd
cmd.Execute ,,128 'use 128 to specify that no records are returned
Set cmd = Nothing
End Sub



*I'm going to continue illustrating using dynamic sql as you have done -
however, I recommend that you switch to using saved parameter queries

HTH,
Bob Barrows

 

[Michel Walsh]

Hi,


Yes, it is per connection. After all, you do not want the @@Identity from an
append of another user, isn't it? So, you have to maintain the connection
open as least as long as you used @@identity. Pooling may work, but if
another user recuperate yours, ... that is potentially "unsafe", even if
@@Identity is kept with the pooling, since you can recuperate the connection
of someone else.


Vanderghast, Access MVP

 

[Michel Walsh]

Hi,

Should be a matter of where you used it. If you used it in the query
designer, it won't work, if you use it with DAO, it won't work. You have to
use ADO and the same connection you used to append the data. The following
work, in Access, in the immediate (debug) window (Access 2000, or more
recent, Access the application, maintains a permanent ADO connection to the
database, Jet or MS SQL Server, called CurrentProject.Connection) :


===========
CurrentProject.Connection.Execute "CREATE TABLE demo( f1 COUNTER, f2
varchar(50));"
CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES ( 'aaa') ; "
CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES (
@@Identity ) ; "

? CurrentProject.COnnection.Execute("SELECT * FROM demo").GetString()
1 aaa
2 1

===========



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,



To continue on Bob idea, using a stored procedure is a way to be sure that
the same connection is used. Note that in general, that kind of stuff has to
be embedded into a transaction (so if one update fail, the whole stuff
fails), so, without T-SQL, all in VBA (so it works even for Jet) :

myAdoConnection.Open ' if you wish to open "as late as possible"
On Error Resume Next
With myAdoConnection
.BeginTrans
.Execute " .... " ' the first insert here
.Execute " .... VALUES( @@Identity, .... ) "
If 0 = Err.Number then
.CommitTrans
Else
. RollbackTrans
End If
End With
On Error ... ' whatever appropriate
myAdoConnection.Close 'if you wish to "close as soon as
possible"



Hoping it may help,
Vanderghast, Access MVP

 

[mark4asp]

Thanks. I wanted to do this in 2 SQL calls rather than 3 but I can see
it is forlorn.

This time I decided to keep the same command object as well as
connection.

I got the same error I had last time. The record was INSERTed but the
link_ID field is not updated. I shall have to do it with 3 SQL calls
rather than 2. That is annoying because previously 2 calls were
sufficient. i.e. when using:

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs)

rather than:

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID = @@IDENTITY

========== ========== ==========
Error Msg:
========== ========== ==========

Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'blog_ID =
@@IDENTITY'.
/if_blog_Local/admin/editBlog.asp, line 43

========== ========== ==========
ASP Code:
========== ========== ==========

Set conn = getAdoConn("Blog")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
cmd.CommandText = sSQL
cmd.Execute
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = @@IDENTITY"
cmd.CommandText = sSQL
cmd.Execute
Set cmd = Nothing
KillConn conn

========== ========== ==========

The getAdoConn and KillConn functions are the same as previously.

 

[mark4asp]

Thanks, your version didn't work (opening the recordset that way).

I finally managed to get this to work using your doAdoSqlCmd 128
argument. I shall now attempt to try it with a transaction - just
because it can, in theory, be done.


Set conn = getAdoConn("Blog")

sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
doAdoSqlCmd conn, sSQL

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = "Select @@IDENTITY"
Set rsBlogs = Server.CreateObject("ADODB.Recordset")
rsBlogs.Open cmd, ,0, 1
iBlogID = rsBlogs(0)
rsBlogs.close
Set rsBlogs = Nothing
Set cmd= Nothing

sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = " & iBlogID
doAdoSqlCmd conn, sSQL

conn.close
Set conn= Nothing

 

[Bob Barrows]

This does not seem to work with an UPDATE statement. You are right that
@@IDENTITY can be used in the VALUES clause of an INSERT query, but when I
try it in an UPDATE statement, I get the same syntax error as the OP.

Bob

 

[Bob Barrows]

You do realize that Max(blog_ID) may not give you the correct result in a
multi-user environment, right?

Hmm, I went to try to recreate your problem and I just noticed what you were
doing: why do you want two columns in the same table with the same data (the
blog_ID)? Very strange.

Also, why bother with a Command object? Why not just use the connection's
Execute method?

Anyways, I just tried myself: I cannot do this in less than 3 steps, either.
It looks like the only places @@IDENTITY is supported is in a standalone
select statement (it wouldn't even work with a subquery) or in an insert
statement's values clause.

Here's the code that worked for me:
dim cn,lRecs, sSQL, newID,rs
set cn=server.CreateObject("adodb.connection")

cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("db7.mdb")
sSQL = "INSERT INTO tblBlogs(body) " &_
"VALUES('test')"
cn.Execute sSQL,lRecs,129
if lRecs > 0 then
Response.Write lRecs & " record was inserted<BR>"
set rs= cn.Execute("Select @@IDENTITY",,1)
newID = rs(0).value
rs.close
set rs=nothing
'Response.Write sSQL & "<BR>"
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " & _
"WHERE blog_ID = " & newID
lRecs = 0
cn.Execute sSQL,lRecs,129
Response.Write lRecs & " record was updated"
end if
cn.Close
set cn=nothing

Sorry, it seems that @@IDENTITY is not as well implemented in Jet as it is
in SQL Server. I guess this is just another price to pay for using a Jet
database in this environment ...

As an alternative, you can use a recordet for the update (ugh!)

Bob Barrows

 

[mark4asp]

You do realize that Max(blog_ID) may not give you the correct result in a
multi-user environment, right?

Yes. That's why I must use @@IDENTITY

Hmm, I went to try to recreate your problem and I just noticed what you were
doing: why do you want two columns in the same table with the same data (the
blog_ID)? Very strange.

The tblBlogs is a threaded discourse list. A parent record has Blog_ID
= Link_ID within the same record. A child element has Link_ID =
Blog_ID value of another record - which will be the parent of that
particular thread.

This is useful as it means each thread can be easily selected and the
entire table can be sorted on Blog_ID within Link_ID to give the
threads in chronological order (assuming Blog_ID to be autonumber)

Also, why bother with a Command object? Why not just use the connection's
Execute method?

I couldn't get the syntax right. I kept getting an error so I turned
to using something that had worked for me before.

I hate the way Microsoft have done ADO - more than one way to do the
same thing - with the result that I can't figure out what anything
does and I need to collect routines. to do these tasks. The example
code you provided didn't work (but thanks anyway).

Anyways, I just tried myself: I cannot do this in less than 3 steps, either.
It looks like the only places @@IDENTITY is supported is in a standalone
select statement (it wouldn't even work with a subquery) or in an insert
statement's values clause.

Infuriating isn't it?

I suppose I should be using MSDE as I can't afford SQL Server but it's
a question of what hosts provide for - which is why there are so many
Access and mySQL sites - not the best databases but they're more
widely available than potentially useful inexpensive databases like
firebird and postgres. I need something with good SQL as well as a
front end that my users can use too, when they may need to browse the
database off-site, perhaps during down time. They're currently using
Access - so that's what I must write in.

 

[Bob Barrows]

On Sat, 13 Sep 2003 15:32:08 -0400, "Bob Barrows"

I hate the way Microsoft have done ADO - more than one way to do the
same thing - with the result that I can't figure out what anything
does and I need to collect routines. to do these tasks. The example
code you provided didn't work (but thanks anyway).

It worked fine for me. what went wrong when you tried it?

Infuriating isn't it?

Nah, not for me - I don't use Access so it's not really a problem for me ;-)

Bob

 

[Michel Walsh]

Hi,


Use [ ] around it seems to solve the problem (still using the previous
table demo):


=============
CurrentProject.Connection.Execute "INSERT INTO demo(f2) VALUES( 'b' ); "

CurrentProject.Connection.Execute "UPDATE demo SET f2='c' WHERE
f1=[@@IDENTITY] "

? CurrentProject.Connection.Execute("SELECT * FROM demo").GetString

1 aaa
2 1
3 c
=============


Hoping it may help,
Vanderghast, Access MVP

 

[Bob Barrows]

Ahh! I did not think of that! What made you try it?
Thanks!

Bob

Hi,


Use [ ] around it seems to solve the problem (still using the
previous table demo):


=============
CurrentProject.Connection.Execute "INSERT INTO demo(f2) VALUES( 'b'
); "

CurrentProject.Connection.Execute "UPDATE demo SET f2='c' WHERE
f1=[@@IDENTITY] "

? CurrentProject.Connection.Execute("SELECT * FROM demo").GetString

1 aaa
2 1
3 c
=============


Hoping it may help,
Vanderghast, Access MVP

This does not seem to work with an UPDATE statement. You are right
that @@IDENTITY can be used in the VALUES clause of an INSERT query,
but when I try it in an UPDATE statement, I get the same syntax
error as the OP.

Bob

 

[Michel Walsh]

Hi,


Since the variable was somehow recognized under some circumstances, I
suspect ADO, or Jet, was "trying to help too much" in the actual
circumstances... One way to stop it to do that was then to tell it "accept
it as is it, without trying to interpret it"... and the experimentation just
proved the intuition was not that bad... (if may be irrelevant, as any
intuition... )


Vanderghast, Access MVP

Ahh! I did not think of that! What made you try it?
Thanks!

Bob

Hi,


Use [ ] around it seems to solve the problem (still using the
previous table demo):


=============
CurrentProject.Connection.Execute "INSERT INTO demo(f2) VALUES( 'b'
); "

CurrentProject.Connection.Execute "UPDATE demo SET f2='c' WHERE
f1=[@@IDENTITY] "

? CurrentProject.Connection.Execute("SELECT * FROM demo").GetString

1 aaa
2 1
3 c
=============


Hoping it may help,
Vanderghast, Access MVP

This does not seem to work with an UPDATE statement. You are right
that @@IDENTITY can be used in the VALUES clause of an INSERT query,
but when I try it in an UPDATE statement, I get the same syntax
error as the OP.

Bob

Michel Walsh wrote:
Hi,

Should be a matter of where you used it. If you used it in the query
designer, it won't work, if you use it with DAO, it won't work. You
have to use ADO and the same connection you used to append the data.
The following work, in Access, in the immediate (debug) window
(Access 2000, or more recent, Access the application, maintains a
permanent ADO connection to the database, Jet or MS SQL Server,
called CurrentProject.Connection) :


===========
CurrentProject.Connection.Execute "CREATE TABLE demo( f1 COUNTER, f2
varchar(50));"
CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES (
'aaa') ; " CurrentProject.Connection.Execute "INSERT INTO demo (f2)
VALUES ( @@Identity ) ; "

? CurrentProject.COnnection.Execute("SELECT * FROM
demo").GetString() 1 aaa
2 1

===========



Hoping it may help,
Vanderghast, Access MVP



It didn't work.

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID = @@IDENTITY

I get this error when trying to save an Access query:

syntax error (missing operator) in
query expression blog_ID = @@IDENTITY

Note: blog_ID is an autonumber key for tblBlogs and link_ID is a
Long Integer field in the same table. The Access 2002 database had
SQL Server Compatible Syntax (ANSI 92) checked in Options.

The INSERT statement does worked OK. So there was an @@IDENTITY
created in that session.

========== ========== ==========
ASP Code:
========== ========== ==========

Set conn = getAdoConn("Blog")
sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
doAdoSqlCmd conn, sSQL
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = @@IDENTITY"
doAdoSqlCmd conn, sSQL
KillConn conn

Function getAdoConn(db)
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout = 30
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
Server.MapPath("..\database\" & db & ".mdb;") &_
"User ID=Admin;Password=;"
Set getAdoConn = conn
End Function

Sub doAdoSqlCmd(conn, sqlcmd)
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = sqlcmd
cmd.Execute
Set cmd = Nothing
End Sub

Sub KillConn(cn)
cn.Close
Set cn = Nothing
End Sub

========== ========== ==========
End Code.
========== ========== ==========


On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"

Hi,


You don't need to "select" it. If you use ADO ( not DAO, not
the query designer), the following should work:

CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2
WHERE f3=@@IDENTITY"


Note that @@IDENTITY is maintained by connection. You have to
use the same connection object that you did something to "assign"
the @@IDENTITY to something, that to get it back. As a trivial
example, but worth of mention, @@IDENTITY of the
currentProject.Connection won't be aware of the latest DAO
operations.



Hoping it may help,
Vanderghast, Access MVP


Is it possible to use a SELECT @@IDENTITY statement as a
sub-query in an UPDATE ?

For instance I wanted to run the following query but Access would
not allow me to enter it:

I've tried each of the following:

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS
iBlogID);

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);

Why is it not possible to do this?

The following does work:

UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM
tblBlogs);

 

[Chris Hohmann]

Hi,


Use [ ] around it seems to solve the problem (still using the previous
table demo):

In a word ... awesome!

Here's a little trivia. Did you know that Access97 had undocumented
support for subqueries?

SELECT
FOO
FROM
[
SELECT TOP 10
FOO
FROM
BAR
ORDER BY FOO DESC
]. AS LastTen
ORDER BY FOO

For the OP, here's a thought. When dealing with hierarchical structures,
I like to create a "root" node. This way I can set the default value of
ParentID to NodeID of the root. With this construct in place, you would
only need one statement to insert a new node. No multiple-statements, no
transactions, no muss, no fuss. Finally, you may want to consider a
"caterpillar walk" for tree structures. Do a google search on "Joe
Celko" and "Trees". Very enlightening stuff.

HTH
-Chris