0

I need to do the below update through macros,

If tbname = "PARTY" Then
Dim ssql As String
ssql = "Update PARTY Set PARTY_ID= (SELECT (max(PARTY_ID)+1) FROM PARTY) WHERE PARTY_ID ='DUMMY'
DB.Execute ssql, 64
End If

I am getting this error, while running the above statement.

Error Code: 1093. You can't specify target table 'PARTY' for update in FROM clause 0.000 sec

Is there any other way to update the max(party_id)+1 to the row having party_id as "DUMMY"

Vogel612
  • 5,620
  • 5
  • 48
  • 73
Anand
  • 13
  • 6

1 Answers1

0

The error you're having is in your SQL. You shouldn't be using a subselect here. The from-clause you're having is complete bull, because Update PARTY implies From PARTY

Additionally you seem to be calculating something strange, since you get an "invalid use of group function"-error. Short googling reveals this stackoverflow question, suggesting you should be using HAVING instead of WHERE

The fix is thusly simple:

ssql = "Update PARTY set PARTY_IT=(max(PARTY_ID) + 1) HAVING PARTY_ID='DUMMY'"
Community
  • 1
  • 1
Vogel612
  • 5,620
  • 5
  • 48
  • 73
  • Update PARTY Set PARTY_ID = (MAX(PARTY_ID*1)+1) WHERE PARTY_ID = '10083' its throwing error as " invalid use of group function" – Anand Feb 05 '15 at 11:34
  • i tried this and its working :) ssql = " Update PARTY Set PARTY_ID = (SELECT MAX(PARTY_ID*1)+1 FROM (SELECT * FROM PARTY) AS PARTY_ID ) WHERE PARTY_ID = 'DUMMY' " – Anand Feb 05 '15 at 11:35