0

This is SQL Server - I have 3 rows of data in a single table based on location I want the data for a single field to match in all three locations e.g I have parts in three locations, location 1, 2, and 3 but the price for the part is different in each of the three locations, I would like to sync the price for all three locations

This is table 1 - fields include item number, location, price. e.g

item    loc.    price
123     1       0.1
123     2       0.2
123     3       0.3

My desired outcome is that location 2 and 3 will have the same price as location 1.

closed as unclear what you're asking by RDFozz, MDCCL, Scott Hodgin, mustaccio, Tom V Mar 22 '18 at 7:49

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    UPDATE myTable SET price = 123.45? I suspect we need more details on the actual tables involved, and how they're related. – RDFozz Mar 21 '18 at 15:46
  • 1
    If you need a single price for all locations, store it once, in a different table. – ypercubeᵀᴹ Mar 21 '18 at 21:57
2

Is this what you're looking for?

Set up demo table with data

declare @T table (ItemNumber int, location int, price decimal(11,2))

insert into @T (ItemNumber,location, price) values
(1,1,10.00),(1,2,20.00),(1,3,30.00)

select * from @T

| ItemNumber | location | price |
|------------|----------|-------|
| 1          | 1        | 10.00 |
| 1          | 2        | 20.00 |
| 1          | 3        | 30.00 |

The update statement

UPDATE @T
SET price = (
        SELECT price
        FROM @T
        WHERE location = 1
        )
WHERE location <> 1

select * from @T

| ItemNumber | location | price |
|------------|----------|-------|
| 1          | 1        | 10.00 |
| 1          | 2        | 10.00 |
| 1          | 3        | 10.00 |
  • This works perfectly - thank you so much!! – Melissa Mar 21 '18 at 18:34
  • 1
    @Melissa - if you think I have sufficiently answered your question, consider accepting it. – Scott Hodgin Mar 21 '18 at 18:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.