Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Puzzling SQL Update Statement Expand / Collapse
Author
Message
Posted Sunday, October 20, 2013 3:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 20, 2013 3:13 PM
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



  Post Attachments 
ScreenShot037.bmp (31 views, 636.69 KB)
Post #1506516
Posted Monday, October 21, 2013 4:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,891, Visits: 2,329
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/
Post #1506613
Posted Monday, October 21, 2013 4:51 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 747, Visits: 1,315
some data would help a great deal
Post #1506617
Posted Monday, October 21, 2013 7:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 3,645, Visits: 7,963
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1506679
Posted Monday, October 21, 2013 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:38 AM
Points: 33,267, Visits: 15,436
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1506745
Posted Tuesday, October 22, 2013 7:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:19 AM
Points: 281, Visits: 1,063
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)

Post #1507140
Posted Tuesday, October 22, 2013 8:47 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:19 AM
Points: 281, Visits: 1,063
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)

Post #1507192
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse