UPDATE lot of registers

[Emiliano Boragina]

Hello everyone…

 

I dont know how to update a lot of registers on one table.

 

I have two tables: Students and Signatures

 

STUDENTS has got: id / name / signature

SIGNATURES has got: id / name / classroom

 

I want to use words and not numbers to write the signature on the STUDENTS
table.

I’ve got a long list with signatures, and I must to change one signature...
How do I do update SIGNATURE AND STUDENTS tables o sam time if 100 students
has got the same signature and must update automatically?

 

Thanks a lot

 

+                                                                          _
   // Emiliano Boragina _
   // Diseño & Comunicación //////////////////
+                                                                          _
   // emiliano.boragina@...  /
   // 15 40 58 60 02 ///////////////////////////
+                                                                          _

 

[chris smith-9]

Emiliano Boragina wrote:

> Hello everyone…
>
>  
>
> I dont know how to update a lot of registers on one table.
>
>  
>
> I have two tables: Students and Signatures
>
>  
>
> STUDENTS has got: id / name / signature
>
> SIGNATURES has got: id / name / classroom
>
>  
>
> I want to use words and not numbers to write the signature on the STUDENTS
> table.
>
> I’ve got a long list with signatures, and I must to change one signature...
> How do I do update SIGNATURE AND STUDENTS tables o sam time if 100 students
> has got the same signature and must update automatically?

The better question would be why do you need to update two tables with
the same information?


Updating two tables at the same time will require using a transaction.
If you're using mysql, this means the table has to be an 'innodb' table.
The default 'myisam' doesn't support transactions.

If you don't use a transaction, you'll potentially lose your changes.

update signatures set name='New name here' where id in
(1,2,3,4,5,6,7,8,9,10);

server dies (power loss, hard drive crash, someone reboots it) after it
updates record 5 but before it updates record 6.

You now have half the table updated.

Even worse would be it updates the first table but not the second - now
your data is inconsistent.


You can update multiple records at once quite easily.

Assuming 'id' is a unique (or primary) key:

Begin;

update signatures set name='New name here' where id in
(1,2,3,4,5,6,7,8,9,10);
update students set name='New name here' where id in (1,2,3,4,5,6,7,8,9,10);

commit;

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[chris smith-9]

[ please always send to the mailing list ]

Emiliano Boragina wrote:
> Hi Chris, thanks for your ask...
> This doubt was beacause I have two tables... one with categories (category
> id and category name), and the other with products (product id, product
> name, category id which the prod belong). The client, sometimes, change the
> category name. To there no problem using the in the in the products table.

I'm still confused.

If you're changing the category name, you only need to change the
categories table.

update categories set name='new name here' where category_id='X';

> But, I must do a finder to search and list the products under category name
> which it belongs, it name and other items... So the finder is not so easy to
> do...

To understand this, it'd be easier in two steps to start off with:

1) Get the category_id:
select category_id from categories where category_name='XXXXXXXXXX';

2) Then get the products:
select product_id, product_name from products where category_id='X';


That can be combined in to one query:

select product_id, product_name from products p inner join categories c
on (p.category_id=c.category_id) where c.category_name='XXXXXXXXXX';

Or am I still missing a piece of the puzzle?

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php