Puzzling SQL Update Statement

Question

Puzzling SQL Update Statement

waqqas.zia

SSC-Addicted

Points: 462
More actions
October 20, 2013 at 3:07 pm

#299335

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

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/

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Umer Akram

SSC-Insane

Points: 22209

October 21, 2013 at 4:51 am

#1660080

some data would help a great deal

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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 - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

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/

below86 SSChampion Points: 11532 More actions · Answer 5

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
Don't fear failure, fear regret.

below86 SSChampion Points: 11532 More actions · Answer 6

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
Don't fear failure, fear regret.