need to update the DB table through macros

Question

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"

you forgot a closing quote in your ssql declaration. – Vogel612 Feb 05 '15 at 10:59 — Vogel612, Feb 05 '15 at 10:59

score 0 · Answer 1 · edited May 23 '17 at 12:28

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'"

edited May 23 '17 at 12:28

Community

1
1

answered Feb 05 '15 at 10:59

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

need to update the DB table through macros

1 Answers1