update...

[Nova980]

How can I update two tables with the same record using different id's?

First record:

1 Mexico Cancun

Second record:

2 Mexico Cancun

The first columns id does not match the column id located in another table. I'd like to update by id, whenever someone updates existing records across both tables. So if table one is updated so is table 2. Also, there are discrepancies between the existing columns across both tables. They are the same record but do not match identically.

 select @Id = @ExistingId
       update Country set country_nm = @country_nm, 
       city_nm = @city_nm
       where id = @id

I'm calling this inside a stored proc that updates table one but since table two does not have the same id it is not updated. Thank you for your patience.

 

[gmmastros]

Your table structures are bad.

Instead of having your string data in two places, you should normalize your tables to correct this problem. For example, there should be a table that has a unique id for each country/state combination. Then, your two existing tables should NOT have country/state, but should have an id that is pointing to the new table instead.

I strongly encourage you to do a little reading on [google]database normalization[/google]. You'll be glad you did.

If you cannot change your table structures, then you should expect to have data corruption issues. You see, country names are unique, but city names are not. Even within countries, there can be duplicate city names (especially true here in the United States).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Good point, George! I was thinking how to solve the problem, but if we would not create such problem at the first place, they would be nothing to solve.