We help IT Professionals succeed at work.

how to update a specific record in the relational tables

I have 4 tables:
sale
location
product
move

when inserting a record, will use the uniq location_id , product_id to insert into 2 tables: sale and move


--the requirement is that select a product_name and location_id to search all related records in the database.
 
--and then can  select an individual record to update it

issue,  i can get 1 more sale_id and move_id when joining these tables, it is hard to pick the record to update.( we are using .net at fron_end)

select * from sale,product,location,move
where sale.product_id=product.product_id
and sale.location_id=location_id
and move.location_id=location.location_id
 
sample-ERD.bmp
Comment
Watch Question

It looks like it should be easy to figure out which one record to update from within the .Net code.

If product_name is updated, sale and product tables
If location is updated, location table
If address is updated, move table

Am I misunderstanding what you are asking?
first, search all the records giving the location_id and product_name

using this query:
select * from sale,product,location,move
where sale.product_id=product.product_id
and sale.location_id=location_id
and move.location_id=location.location_id

second , select 1 record from the result set (maybe many records) to update

what i am asking is beause there are many records there, and also sale_id and move_id are dupliate,

how to pick up a purticular record to perform update( using .NET)?
is the result correct when joining the tables?

thanks

Top Expert 2010
Commented:


I am making a picture of this requirement and see if the below is what you want and if it will work.

Basically you are viewing the sale. to get the other details you left join other tables to sale.

select * from sale
left join product on product.product_d = sale.product_id
left join location on location.location_id = sale.location_id
left join move on move.location_id = sale.location_id

when u display this in ui and user changes location and/or product, basically he changes the detail of a sale.  so always update sale table.
when user selects another location, sale's location_id needs to be updated.

update sale
set location_id = {user selected location id}

you can add product id update as well.

Top Expert 2010
Commented:

Another related point: it is better to use the column names in select sql not * (in ms sql i think this sql will throw name conflict error)

Author

Commented:
thanks guys

i use this SQL
select * from sale,product,location,move
where sale.product_id=product.product_id
and sale.location_id=location_id
and move.location_id=location.location_id


IS THE SAME AS
select * from sale
left join product on product.product_d = sale.product_id
left join location on location.location_id = sale.location_id
left join move on move.location_id = sale.location_id




Top Expert 2010
Commented:

Yes. both the sqls will give the same results; you can use either one.

Regarding my comment (ID:32965760) on using select *; I just checked; it doesnt give any name conflict error...

As i menioned earlier, my understanding on the requirement is: user is basically viewing the sale and it's location, etc...when user changes a location, its the sale table that needs to be updated. same for product. Updating the location/product tables typicall would happen when the user wants to change the name of a location/product which I think would be in another Edit Location control/view and in Edit Product control/view. If your requirement is different from this, then this needs to be started over again...