MYSQL QUERY to retrieve the data

Question

I have the following schema with the following attributes:

USER(TABLE_NAME)
USER_ID|USERNAME|PASSWORD|TOPIC_NAME|FLAG1|FLAG2

I have 2 questions basically:

1. How can I make an attribute USER_ID as primary key and it should automatically increment the value each time I insert the value into the database.It shouldn't be under my control.

2. How can I retrieve a record from the database, based on the latest time from which it was updated.( for example if I updated a record at 2pm and same record at 3pm, if I retrieve now at 4pm I should get the record that was updated at 3pm i.e. the latest updated one.)

Please help.

Answer 1

I'm assuming that question one is in the context of MYSQL. So, you can use the ALTER TABLE statement to mark a field as PRIMARY KEY, and to mark it AUTOINCREMENT

ALTER TABLE User
   ADD PRIMARY KEY (USER_ID);

ALTER TABLE User
   MODIFY COLUMN USER_ID INT(4) AUTO_INCREMENT;   -- of course, set the type appropriately

For the second question I'm not sure I understand correctly so I'm just going to go ahead and give you some basic information before giving an answer that may confuse you.

When you update the same record multiple times, only the most recent update is persisted. Basically, once you update a record, it's previous values are not kept. So, if you update a record at 2pm, and then update the same record at 3pm - when you query for the record you will automatically receive the most recent values.

Now, if by updating you mean you would insert new values for the same USER_ID multiple times and want to retrieve the most recent, then you would need to use a field in the table to store a timestamp of when each record is created/updated. Then you can query for the most recent value based on the timestamp.

Answer 2

I assume you're talking about Oracle since you tagged it as Oracle. You also tagged the question as MySQL where the approach will be different.

You can make the USER_ID column a primary key

ALTER TABLE <<table_name>>
  ADD CONSTRAINT pk_user_id PRIMARY KEY( user_id );

If you want the value to increment automatically, you'd need to create a sequence

CREATE SEQUENCE user_id_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

and then create a trigger on the table that uses the sequence

CREATE OR REPLACE TRIGGER trg_assign_user_id
  BEFORE INSERT ON <<table name>>
  FOR EACH ROW
BEGIN
  :new.user_id := user_id_seq.nextval;
END;

As for your second question, I'm not sure that I understand. If you update a row and then commit that change, all subsequent queries are going to read the updated data (barring exceptionally unlikely cases where you've set a serializable transaction isolation level and you've got transactions that run for multiple hours and you're running the query in that transaction). You don't need to do anything to see the current data.

Answer 3

(Answer based on MySQL; conceptually similar answer if using Oracle, but the SQL will probably be different.)

1. If USER_ID was not defined as a primary key or automatically incrementing at the time of table creation, then you can use:

   ALTER TABLE tablename MODIFY USER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

2. To issue queries based on record dates, you have to have a field defined to hold date-related datetypes. The date and time of record modifications would be something you would manage (e.g. add/change) based on the way in which you are accessing the records (some PHP-related way? it's unclear what scripts you have in play, based on your question.) Once you have dates in your records you can ORDER BY the date field in your SELECT query.

Answer 4

1. You can use something like this:

   CREATE TABLE IF NOT EXISTS user ( USER_ID unsigned int(8) NOT NULL AUTO_INCREMENT, username varchar(25) NOT NULL, password varchar(25) NOT NULL, topic_name varchar(100) NOT NULL, flag1 smallint(1) NOT NULL DEFAULT 0, flag2 smallint(1) NOT NULL DEFAULT 0, update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (uid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

2. For selection use query:

   SELECT * from user ORDER BY update_time DESC

Answer 5

1. Check this out For your AUTOINCREMENT, Its a question already asked here
   For your PRIMARY KEY use this ALTER TABLE USER ADD PRIMARY KEY (USER_ID)

2. Can you provide more information. If the value gets updated you definitely do NOT have your old value that you entered at 2pm present in the dB. So querying for it will be fine