updating data in sql

[seger]

hi,

I need to update my data in two tables simultaneously,for example if i update product id in one aspx page it should be updated in my sql tables where i use product id in all my pages

we can join tables to view data but how to update..
hope my doubt is clear.

[rch1231]

Hello,

I am not sure what you are trying to update but the following sql would update fields in two separate tables:

UPDATE table1, table2
set table1.field1 = 1,
table2.field2 = table1.field1
where table1.field1=table2.field2

Not the greatest example in the world but I think it will give you the idea.

[seger]

let me explain u clearly..
I have a table name product Details I have Pid as a field.
then another table name is product order so if i update my Pid in product details simultanoeusly in product order also have pid,it should be also updated...how to do that..hope its clear for u now?

[rch1231]

That really sounds like what you use triggers for. I am curious why you would ever change a products PID. Isn't that the key for the Product table? You might change the description or cost or unit of measure but the PID should be unique.

[tesuji]

let me explain u clearly..
I have a table name product Details I have Pid as a field.
then another table name is product order so if i update my Pid in product details simultanoeusly in product order also have pid,it should be also updated...how to do that..hope its clear for u now?

This objective can easily be achieved by the table's definitions. You should make use of properly defined foreign keys, as for example:

create table productdetails(pid integer not null, description varchar(100), ...further data..., 
primary key(pid));

create table productorder (shouldhaveprimarykeytoo integer not null, pid integer, 
...further data...,
primary key(shouldhaveprimarykeytoo),
[B]foreign key (pid) references productdetails(pid) [U]ON UPDATE CASCADE[/U][/B]);

Now if pid changes in master table productdetails its reference in table productorder will automatically be changed due to update policy CASCADE.

Well, as already stated, changing values of primary keys is a severe issue and should not be daily business for it requires such dangerous policies as cascade which can easily destoy data consistence of a whole database.

-- tesu