update table

[adishardis]

Hi,

I have a table and I want to create a field to show whether it's the first occurrence (has the oldest date) of the id in that table.

the table looks something like this and it's the last field I need help with creating a update code for.

----id----date_of_trans------custom_id--------cust_type-----
   1141    2013-08-12      11412013-08-12        new
   1141    2013-08-17      11412013-08-17     recurring

So only the first(oldest) entry of an id should have cust_type 'new' the rest should be 'recurring'.

Any advice would be helpful!

Chees
Adam

[broj1]

In my opininion you do not need the cust_type field since it is redundant which is against the database normalization rules. You can always get the oldest one with:

SELECT id FROM thetable ORDER BY date_of_trans ASC LIMIT 1

[Rahul47]

Compare the date and assign cust_type.

[ruhestorer]

UPDATE *table_name*
SET cust_type='new'
WHERE custom_id= (SELECT custom_id FROM *table_name* WHERE AND id=*id_code* ORDER BY date_of_trans DESC LIMIT 1)
GO
UPDATE *table_name*
SET cust_type='recurring'
WHERE custom_id!= (SELECT custom_id FROM *table_name* WHERE AND id=*id_code* ORDER BY date_of_trans DESC LIMIT 1)
AND id=*id_code*

[broj1]

What database are you using? ruhestorer's example is nice only that I am not sure if GO is supported in mysql. I think semicolon ; might work the same.

[ruhestorer]

Sorry, I'm working with MySQL, MS SQL and PL/SQL at the same time, so sometimes it gets messy. The above example is in MS SQL. But i think that you're right about semicolon.

[adishardis]

Thanks for all your suggestions!

My database is mysql and I'm trying to write this in php add it as a cronjob every night.

In your code what do you mean by id_code?

[broj1]

I do not want to speak on behalf of ruhestorer, it just seems to me that id_code is the particular selected id you are updating the table for. Like, you can read all the unique IDs in an array and the loop through array and run the above SQL statement. And it would probably be enough to just insert into the rows that have the new value (to run just the first query) and leave the others empty or NULL.

Anyway, I still have a feeling that there might be a sloution without using the redundant cust_type field :-)

[ruhestorer]

Broj1 was true about:

it just seems to me that id_code is the particular selected id you are updating the table for.

And i agree on:

Anyway, I still have a feeling that there might be a sloution without using the redundant cust_type field :-)

But if you really want to use cust_type field, just don't do it using text (it's not a good style, especially if you using just 2 values), make it int and type 1 if new and 0 as recurring, and you could set 0 as default.

[adishardis]

Ok, I think I get it but when I apply your code above I get the following error ouput:
You can't specify target table 'cust_table' for update in FROM clause

then I tried specifying another table and that doesn't give me any errors but doesn't update the table either...

So I tried to use your code and join the tables like this:

UPDATE tableONE t1 
JOIN tableTWO t2
    ON t2.id = t1.id
SET t1.cust_type=1
WHERE t1.custom_id != (SELECT t2.custom_id FROM tableTWO t2 WHERE t2.id=t1.id ORDER BY t2.disbursement_date desc LIMIT 1)")or die(mysql_error());

When I used the code above the script stalls after a while with the folling error output:
General error: 2013 Lost connection to MySQL server during query

it does however give me the desired result, until it halts and makes it incomplete.

btw. why is making it int better?

Thanks for your patience and cheers!!
/Adam

[broj1]

OK, I had a closer look at it This is what mysql manual says about updating the same table that is used in a subquery:

In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE

See: http://dev.mysql.com/doc/refman/5.5/en/subqueries.html

I am not an expert on this stuff, maybe someone else might help (I will keep trying). And I do not know how JOIN might help here.

One question though: why do not enter the new/recurring at the time of inserting the record (i.e. using a trigger)?

[broj1]

Just to add to above. This problem is easily solved using PHP. You first read the oldest row for a particular ID, select it's custom_id and update the cust_type to 'new'. Then just update the other rows with this id to 'recurring'.

// you have an ID
$id = 1141;

// find the oldest entry for this ID
$query = "SELECT cutom_id FROM <whatever_the_table> WHERE id='$id' ORDER BY date_of_trans ASC LIMIT 1";
... 

// upon querying the database you get oldest custom_id
$oldest_custom_id = <some_value>;

// now update the oldest row
$query = "UPDATE <whatever_the_table> SET cust_type='new' WHERE id='$id' AND custom_id='$oldest_custom_id'";

// now update the other rows of the same id
$query = "UPDATE <whatever_the_table> SET cust_type='recurring' WHERE id='$id' AND custom_id!='$oldest_custom_id'";

On the other hand you could use a stored procedure and an mysql event scheduler.