Help with an update statement

Dev Shed Forums Sponsor:

October 29th, 2012, 05:50 AM

lutpoy

Registered User

Join Date: Oct 2012

Posts: 1

Time spent in forums: 1 h 3 m 29 sec
Reputation Power: 0

Help with an update statement

Hi

Firstly, I can will need to run this statement in mysql or access db, either is fine by me as I can do it at source in the access db or in the mysql db I export to.

Basically I want an update statement which adds a sequential number to a table for a select statement. I dont mind if every time I run it that it populates every row again. It does not need to find the last sequence number.

Dont worry about any of the table names etc below being reserved words, ive just done as an example to try and keep as simple as possible.

Ive made these names up so that it makes more sense.
Table name is called TABLEA
Within TABLEA are NAME, UNIQUENUMBER, DATE, SEQUENCE

Basically my primary key so to speak is on NAME, UNIQUENUMBER

What I want to do is write an update statement which populates the SEQUENCE column with a sequential number for the NAME but the sequence must then restart for the next NAME.

I.E My select would be
Select NAME, UNIQUENUMBER
From TABLEA
Order by NAME, UNIQUENUMBER desc

Lets say my data from the select is

NAME UNQIENUMBER
PETER 007
PETER 004
PETER 001
JOHN 101
JOHN 099
GARY 078
GARY 064
GARY 045
GARY 038

I want the update statement to populate the SEQUENCE so that my data would now be

NAME UNIQUENUMBER SEQUENCE
PETER 007 1
PETER 004 2
PETER 001 3
JOHN 101 1
JOHN 099 2
GARY 078 1
GARY 064 2
GARY 045 3
GARY 038 4

I need to achieve this as I then need to run some scripts based on the SEQUENCE for each NAME.

Thanks for your help

October 29th, 2012, 08:35 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

In mysql you can use auto_increment in a compound key - in this case (name,id).

Note that the table ENGINE needs to be MyISAM for this to work, but the manual covers all this.

Alternatively, you could just calculate the value 'on-the-fly':

Code:

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table 
(NAME VARCHAR(12) NOT NULL,UNIQUENUMBER INT NOT NULL,PRIMARY KEY(name,uniquenumber));

INSERT INTO my_table VALUES
('PETER',007),
('PETER', 004),
('PETER', 001),
('JOHN',101),
('JOHN', 099),
('GARY', 078),
('GARY', 064),
('GARY', 045),
('GARY', 038);

SELECT x.name
     , LPAD(x.uniquenumber,3,0) uniquenumber
     , COUNT(*) rank 
  FROM my_table x 
  JOIN my_table y 
    ON y.name = x.name 
   AND y.uniquenumber >= x.uniquenumber 
 GROUP 
    BY x.name
     , uniquenumber 
 ORDER 
    BY name DESC
     , rank;
+-------+--------------+------+
| name  | uniquenumber | rank |
+-------+--------------+------+
| PETER | 007          |    1 |
| PETER | 004          |    2 |
| PETER | 001          |    3 |
| JOHN  | 101          |    1 |
| JOHN  | 099          |    2 |
| GARY  | 078          |    1 |
| GARY  | 064          |    2 |
| GARY  | 045          |    3 |
| GARY  | 038          |    4 |
+-------+--------------+------+

Viewing: Dev Shed Forums > Databases > MySQL Help > Help with an update statement

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump