How to Update and IDX by running frappe.db.sql?

1

Hi all,
Trying to have this
frappe.db.sql(“”" SET @IncrementValue = 0 “”",as_dict=False)
frappe.db.sql(“”" UPDATE tabSales Invoice Item set idx = @IncrementValue:=@IncrementValue+1 where parent = %s “”",x.name,as_dict=False)

But does not work. I have also tried and returns sql error.
frappe.db.sql(“”" UPDATE tabSales Invoice Item SET @IncrementValue = 0, set idx = @IncrementValue:=@IncrementValue+1 where parent = %s “”",x.name,as_dict=False)

Anyone knows a way of running this update using single db.sql?

Rgrds,
:slight_smile:

2

Sorry my fault… was missing just the frappe.db.commit() after using
frappe.db.sql(“”" SET @IncrementValue = 0 “”",as_dict=False)
frappe.db.sql(“”" UPDATE tabSales Invoice Item set idx = @IncrementValue:=@IncrementValue+1 where parent = %s “”",x.name,as_dict=False)