Set value from one to column to another where

Question

Set value from one to column to another where

bzoom100

Ten Centuries

Points: 1092
More actions
January 5, 2017 at 3:23 pm

#320249

Within the same table I need to take the ApplicationNumber value where the Type value = 54 and place it into the ProjectNumber value where the Type value = 72.
Here's what I came up with but it's copying ApplicationNumber value from Type 72 to ProjectNumber value from Type 72.

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

bzoom100 (1/5/2017)
Within the same table I need to take the ApplicationNumber value where the Type value = 54 and place it into the ProjectNumber value where the Type value = 72.
Here's what I came up with but it's copying ApplicationNumber value from Type 72 to ProjectNumber value from Type 72.

I might be going crazy here, but I not sure this makes sense. You want to update a field, ProjectNumber, when [type] has a value of 54 and update it with the Application number when [type] has a value of 72..? [type] can't have both values of 54 and 72, it will be one or the other.

In regards to your query, what you're doing here is two things, but it doesn't seem to fall in line with your above logic

--Return a dataset of all Application Numbers where [type] has a value of '54' in the table tblDatapermit

SELECT ApplicationNumber

FROM dbo.tblDataPermit

WHERE Type = '54'

--End of Query. This will have no affect on the following.

--Update the table tblDataPermit by setting the ProjectNumber to be the ApplicationNumber when [type] has a value of 72

--and Applicationtype has a value of 'Fire Inspection'

UPDATE dbo.tblDataPermit

SET ProjectNumber = ApplicationNumber

WHERE Type = '72'

AND RefNo = RefNo --This statement doesn't achieve anything. It's like saying where 1 = 1 or 'TRUE' = 'TRUE'. RefNo = RefNo is always going to be TRUE.

AND ApplicationType = 'Fire Inspection'

You need to explain your logic here a bit more here. As I said above, your field [type] can't have two values, so I'm unsure what it is you really want to achieve. :hehe:

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

bzoom100 Ten Centuries Points: 1092 More actions · Answer 2

Please check this image for example of what I mean:

https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png

Thom A SSC Guru Points: 99130 More actions · Answer 3

Your image shows that these are two different rows. We're missing a piece of logic here that connects the two. Your statement above will update a column with the value of another column in the same row. How do you connect the dots? 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

bzoom100 Ten Centuries Points: 1092 More actions · Answer 4

Sorry,

This is clearer. The RefNo columns must macth.

https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png

I need to make this update for a few hundred rows and I thought there might be a query so that I don't have to do it manually.

Thom A SSC Guru Points: 99130 More actions · Answer 5

That's the missing piece of information we needed 🙂

Does this work for you?

Update DP

SET DP.ProjectNumber = DP2.ApplicationNumber

FROM dbo.tblDataPermit DP

JOIN dbo.tblDataPermit DP2 ON DP.RefNo = DP2.RefNo

WHERE DP.[Type] = 72

AND DP.ApplicationType = 'Fire Inspection'

AND DP2.[Type] = 54;

For stuff like this, you need to ensure you give the whole picture. DDL really helps, as I had no idea that there was another field that completed your logic. You need to be as clear and concise as possible 😎

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

bzoom100 Ten Centuries Points: 1092 More actions · Answer 6

It worked! Thanks man, you saved me a lot of time. I'm learning and promise to do better here.