Automatic Increment numbers

[Asbestos2]

Hi

I am fairly new to MySql. I have created a table and after a few entries I made the ID into AI. This works OK but a couple of questions;

1. Because I started without AI then did it started the increment at 5. is there away to start it at a number you want it to like 1.

2. Is there a way, for example, when we add anew quote in the quote table we can put a Q in front of the number and start at 100 for example, so the first quote would be Q100.

3. After all testing is there a way to set all the numbers back to start at 1 or 100 in the relevant table.

Thanks

 

[r937]

1. what's AI? auto_increment? you can start it at any number you want, see CREATE TABLE syntax in the manual, and look at the table options at the end of the statement

2. concat the Q in a view

3. yes, using TRUNCATE

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Asbestos2]

Hi

Thanks for the reply

1. Yes I mean the auto increment. As I mentioned I am new to MySql so I have bene using the Workbench 5.2 to create and make my tables. I am not up with the programming side as yet. But i understand about truncate and also a tip I saw on the Internet, if you wanted to start at 100 you put in a false record for 99 for instance then the next number would be 100.

2. I dont understand what you mean by concat a view. Could you give me an example how to do it. The idea is to have Quotes beginning with a Q, then sales order with S and Invoice with I. I would appreciate some help on the concat view.

Thanks

 

[r937]

CREATE VIEW myview
AS
SELECT CONCAT('Q',id) AS quote_id
, otherstuff
FROM daTable



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Asbestos2]

Hi

I ahve tried what you suggest and for some reason when it tries to run it gives a system error message and asks if I want to send an error report and then closes the Workbench.

the databse is called Test, The table is called quote and to test I ahve one column called idquote set as PK NN and AI.

The View script I have tried is


USE Test;
CREATE VIEW Qview
AS
SELECT CONCAT('Test',quote) AS idquote

FROM Quote


When I click apply it shows

USE ``;

USE ;

The it crashes.

Is the View code correct, I ahve tried many ways round but still I get the crash.

Thanks

 

[Asbestos2]

I got a little further

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW `qview`
AS select concat('Q',`quote`.`idQuote`) AS `idquote`
from `quote`

This applied ok.

I added another field into the Quote table called Name and enetere 2 records with names. The numbers didnt appear until I forced the table to close and then it wanted me to apply the changes.

I went back into the quote table and only the numbers 1 and 2 appeared and no Q in front.

Any ideas what is going wrong (or I am doing wrong).

Thanks

 

[r937]

I went back into the quote table and only the numbers 1 and 2 appeared and no Q in front.

the Q appears ~only~ in the view


you will have to SELECT from the view every time, not from the table

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Asbestos2]

Oh I see yes that works. However the next thought is it possible to use a view through access forms (we are using Access as front end tool).... assuming it is, when we enter data into the Quote it should populate the Quote table from the view (input form access form). is my thinking correct there

Thanks

 

[r937]

i think you might want to ask in forum701

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon