Update field based on another table field value

Question

Post reply

Update field based on another table field value

Viewing 5 posts - 16 through 19 (of 19 total)

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

toddasd SSCertifiable Points: 6298 More actions · Answer 1

You can simplify your CASE expression like this:

CASE table2.Code2

WHEN '1-10' THEN 1

WHEN '11-20' THEN 2

WHEN '21-30' THEN 3

WHEN '31-40' THEN 4

WHEN '41-50' THEN 5

WHEN '51-60' THEN 6

WHEN '61-70' THEN 7

WHEN '71-80' THEN 8

WHEN '81-90' THEN 9

END

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

toddasd SSCertifiable Points: 6298 More actions · Answer 2

Also, to answer the question you posted the DDL for, your update is safe ...in this sample set. If you get the query John provided and run that, you'll see that there are no duplicate keys in table2 matching against table1. So you're safe from an ambiguous update...again, in this sample set.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

branovuk SSCommitted Points: 1774 More actions · Answer 3

Thanks toddasd for comments, and I will do simplification.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Test

toddasd (10/5/2011)
Also, to answer the question you posted the DDL for, your update is safe ...in this sample set. If you get the query John provided and run that, you'll see that there are no duplicate keys in table2 matching against table1. So you're safe from an ambiguous update...again, in this sample set.

Yes, he's safe, given the data provided. But I worry that this is not real data and I wonder what the reason is for having two separate tables, given that there appears to be a one-to-one relationship between them. I'd also like to see what primary key and foreign key constraints there are on the tables.

Here's a demonstration of how different data could cause unexpected results. Thanks to Joe Celko for showing me this originally.

Assuming you're doing this on a test server, start with this:

CROP TABLE table1

CROP TABLE table2

(But change the Cs above for Ds. We seem to have some filter here that blocks out the D-word!)

Now create your tables and insert the data again, then do this:

INSERT INTO table2

(key2, connected_column, Code2, Extra_Condition)

VALUES

(16, 'id5', '81-90','something'),

(17, 'id10', '71-80','something'),

(18, 'id3', '61-70','something'),

(19, 'id4', '51-60','something'),

(20, 'id5', '41-50','something')

CREATE CLUSTERED INDEX CX_key2 ON table2(key2)

Run your UPDATE statement again, then select all the data from table1 and save somewhere safe.

Next, do the whole thing again from the start, except change the index creation statement to this:

CREATE CLUSTERED INDEX CX_Code2 ON table2(Code2)

Compare the two result sets. Notice the difference in the row for key1=8? Don't you think it's scary that changing your indexes can cause different data to end up in your tables? Worse still, you might not know about it until months later.

John

toddasd SSCertifiable Points: 6298 More actions · Answer 5

Assuming you're doing this on a test server, start with this:
CROP TABLE table1
CROP TABLE table2
(But change the Cs above for Ds. We seem to have some filter here that blocks out the D-word!)

I thought I learned a new clean table command. I actually ran it before I read your note :unsure:

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.