how to get multiple rows using select?

Question

I am trying to track all the changes that happened on my table

I tried this query

Update Stocks
Set stockOut = 100
,TrackDate = '1/30/2016'
 ,stockOnHand = stockOnHand - 400
 WHERE itemID = '4589-S';

and this one.Both on Different date.

    Update Stocks
Set stockOut = 200
,TrackDate = '2/30/2016'
 ,stockOnHand = stockOnHand - 400
 WHERE itemID = '4589-S';

Now I used this query

SELECT
[ItemID]
      ,[ProdID]
      ,[stockOnHand]
      ,[stockIn]
      ,[stockOut]
      ,[TrackDate]
 from Stocks
where TrackDate between '1/30/2016' and '5/30/2016'

to track the changes within the specific span of date.But I'm only getting 1 result only

I was Expecting to get 2 results because I've Updated this table in two different dates.

You tagged the question with mysql and the date format you are saving is not correct date. — Abhik Chakraborty
– Abhik Chakraborty, Commented May 30, 2016 at 13:39
if itemID is a PK, then you only have one row for this item so both updates worked with the same row and this is why you get only this row in your select — Enrique Muñoz Rodas
– Enrique Muñoz Rodas, Commented May 30, 2016 at 13:41
I was hoping to get multiple results of changes that happen between that span of time.How could I achieve that? — rai nalasa
– rai nalasa, Commented May 30, 2016 at 13:44

Borjante · Accepted Answer · 2016-05-30 14:17:12Z

1

You need two tables,

STOCK & STOCK_HISTORY

When inserting/updating stocks, you should also create a new record in the STOCK_HISTORY table. The columns of this table should be something like ID, STOCK_ID, ACTION (create, update, delete) and a TIMESTAMP.

This way, you have no useless info in the STOCK table, and whenever you want to list all changes of a stock you do:

SELECT * FROM stocks s
  JOIN stock_history sh ON sh.stockId = s.id
  WHERE s.id = $stockId

answered May 30, 2016 at 14:17

Borjante

10.7k6 gold badges40 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

rai nalasa Over a year ago

So the stock history is like a mirror?I'm kinda getting the idea now.

Borjante Over a year ago

Nope. Stock data is in the STOCK table, data related to stock changes is in the stock_history table. It's diferent data they handle

rai nalasa Over a year ago

so the stockHistory should contain stockID,Update(TimeStamp),stock in,stock out,and stock in hand?

Borjante Over a year ago

For performance reasons, I would just have: ID | STOCKID | UPDATE(timestamp) | ACTION(create, update, delete) | quantity (can be positive or negative)

rai nalasa Over a year ago

So I'm using TimeStamp to generate reports or archivie the date of changes,also timestamp can give me multiple results?

|

O. Jones · Accepted Answer · 2016-05-30 13:38:41Z

0

UPDATE queries change existing rows.

INSERT queries add new rows.

Both your update queries changed the same row.

answered May 30, 2016 at 13:38

community wiki

O. Jones

6 Comments

sagi Over a year ago

But he is right, only 1 row updated, so only 1 row can be on the output. Once it updated, the old version no longer exists.

rai nalasa Over a year ago

@sagi what query could I use that I can view the old versions as well?like see the reports of what happen during specific time span?

sagi Over a year ago

Don't update them, insert them as new row with different date @rainalasa

rai nalasa Over a year ago

will that retain the primary key ID the same?

Borjante Over a year ago

You will neeed an extra table to track changes

|

iLikeMySql · Accepted Answer · 2016-05-30 13:58:45Z

0

To archive every change made to your table, add a TRIGGER.
http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

The trigger will archive the full row or parts of it by writing it to another table in case the row gets deletet or updated. You will have to define inside the TRIGGER-Definition what exactly happens when a row is changed and which values of the row should be archived.

answered May 30, 2016 at 13:58

iLikeMySql

7463 silver badges7 bronze badges

Collectives™ on Stack Overflow

how to get multiple rows using select?

3 Answers 3

7 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related