CODEWITHSUNDEEP
mysqlstored-procedures
Nimmy
Nimmy

Reputation: 5381

Stored procedure - Update Query

I am not familiar with SQL stored procedure.

I have one table called customer and it has one feild called accountNo. i want to update the customer table with new accountNo and the accountNo should be increment from 1000.

I want to update all customers in the table and the accountNo will be like 100,1001,1002.....

Thanks in advance, Nimmy

Upvotes: 1

Views: 199

Answers (2)

matt
matt

Reputation: 4734

Solution provided by Jamiec is almost right, but if there's unique key on accountNumber his first update might not work due to unique key constrains. Consider accoutNumber = 1 and accountNumber = 1001, if the row with accountNumber = 1 will be updated first MySQL will raise error 'duplicate key for row xxx'.

You need to update Customer table in order

UPDATE Customer
SET accountNumber = accountNumber + 1000
ORDER BY accountNumber DESC

Upvotes: 0

Jamiec
Jamiec

Reputation: 136094

This will update every customer in the table

UPDATE Customer
SET accountNumber = accountNumber+1000

That's based on the fact that you said "The customer table has only 1 field - accountNumber". However, what I suspect is that your table actually has a key which represents a customerId (If not, it should!!!)

In which case you probably want to update one specific customer's account number

UPDATE Customer
SET accountNumber = accountNumber+1000
WHERE customerId = 123

Upvotes: 2

Related Questions