Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Puzzling SQL Update Statement


Puzzling SQL Update Statement

Author
Message
waqqas.zia
waqqas.zia
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 79
This update statement is a little puzzling and forgive me if there is a bad explanation but Im puzzled as it is.

In simple terms, I am updating 2 tables.
Table A: contains a heirachy for a location tree (location -> continent-> country)
Table B: contains the location heirachy for the job

So the situation has arisen where some countries are being moved into different continents for some reason or other.
I.E. Egypt is no longer in Africa but moved to middle east.

So my update statement is proving to be a little tricky. I need to update all jobs where the country is egypt but the region is africa. Because each job has 3 rows of data how can i say, if egypt update a different row.

Your help would be greatly appreciated

attached is a simplification of the 2 tables
Attachments
ScreenShot037.bmp (31 views, 636.00 KB)
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2405 Visits: 2763
Can you please provide some more details like table strucutre and result tant ouput and some dumy data?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1366 Visits: 2594
some data would help a great deal
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18081
The best advice I could give you is to normalize your tables. You have an awful design and you're repeating values with no sense. Table B should only have the country and not the continent or value description.
Only if you have no other choice, a CTE or subqueries might help you with your problem. This might be an option.

WITH SampleData( JobNumber, Entry, Tree) AS(
   SELECT 'A1234', 1, 'Location' UNION ALL
   SELECT 'A1234', 5, 'Middle East' UNION ALL
   SELECT 'A1234', 8, 'Egypt' UNION ALL
   SELECT 'A5678', 1, 'Location' UNION ALL
   SELECT 'A5678', 5, 'Africa' UNION ALL
   SELECT 'A5678', 8, 'Egypt' UNION ALL
   SELECT 'B1234', 1, 'Location' UNION ALL
   SELECT 'B1234', 4, 'Europe' UNION ALL
   SELECT 'B1234', 7, 'United Kingdom' UNION ALL
   SELECT 'C1234', 1, 'Location' UNION ALL
   SELECT 'C1234', 3, 'America' UNION ALL
   SELECT 'C1234', 9, 'USA'
)
,Jobs AS(
   SELECT JobNumber
   FROM SampleData
   GROUP BY JobNumber
   HAVING MAX( CASE WHEN Tree <> 'Location' THEN Tree END) = 'Egypt'
   AND MIN( CASE WHEN Tree <> 'Location' THEN Tree END) = 'Africa'
   AND MAX( Tree) = 'Location'
)
UPDATE b SET
   somecolumn = 'Somevalue'
FROM TableB b
JOIN Jobs j ON b.JobNumber = j.JobNumber




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35963 Visits: 18725
Read this and post some DDL/DML and we can likely help. As noted, however, the design probably needs work. If you can't change that, give us some setup and we'll help

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
below86
below86
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 2111
I did't try testing this but would this be what you want?

UPDATE Table_B
SET tree = 'Middle East',
   [entry] = 5
FROM Table_B
WHERE job_number in (SELECT job_number
                  FROM Table_B
                  WHERE tree = 'Egypt'
                  GROUP BY job_number)



-------------------------------------------------------------
we travel not to escape life but for life not to escape us
below86
below86
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 2111
I knew I forgot something, added to the where clause so you don't update to much.

UPDATE Table_B
SET tree = 'Middle East',
   [entry] = 5
FROM Table_B
WHERE tree = 'Africa'
   and job_number in (SELECT job_number
                  FROM Table_B
                  WHERE tree = 'Egypt'
                  GROUP BY job_number)



-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search