Updating data in a table

Question

Post reply

Updating data in a table

Kris-155042

Hall of Fame

Points: 3460
More actions
July 16, 2006 at 10:27 pm

#171594

I need a script that will update the value in column [ACT] in a table where [AC] = 'AA' to the value contained in [ACT] where [ACTION] matches to a record that has [AC] = 'BB' . The column have to be the same for both 'AA' and 'BB'

Example :

Currently
ACTION   AC    CODE    ACT
1             AA    AACS    ZZZ
2             BB    AACS    NULL

Would like to see
ACTION   AC    CODE    ACT
1             AA    AACS    ZZZ
2             BB    AACS    ZZZ

Thanks,
Kris

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

UPDATE T1

SET ACT = T2.ACT

FROM Table T1

INNER JOIN Table T2 ON T1.Action = T2.Action AND T1.Code = T2.Code

WHERE T2.AC = 'AA' AND T1.AC = 'BB'

AND T1.ACT IS NULL -- not sure you want to have this last row in where clause

-- comment it if you need to update not only rows with NULL in ACT.

_____________
Code for TallyGenerator

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 2

Sorry. I should have mentioned it's the same table.

Thanks,

Kris

John Rowan SSC Guru Points: 56440 More actions · Answer 3

What Sergiy has given you will work for the same table. Just replace the work Table in the query (for both T1 and T2) with the same table name. This will create a self-join.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 4

I get the following error when I change the table names to be the same name.

The objects "Table1" and "Table1" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Thanks,

Kris

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Did you delete aliases?

What you need to do is to replace "Table" with real table name. Don't change anything else.

_____________
Code for TallyGenerator

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 6

One of those days I think

Thanks for that, works perfectly.

Thanks,

Kris