Best approach to update 1

[hanchilicious]

Hi,

This is my update statement in SQL 8:

update my_table
set sales_district_id = 
(select salesgroup from my_other_table where my_other_table.salesid = my_table.sales_id)

The trouble is that the my_other_table does not contain all the sales ids that my_table contains. So, using it as a reference leads to this kind of thing:

OLDID	MAPID	MAPVAL
DS12	NULL	NULL
ED18	NULL	NULL
ED19	ER19	ER04
BG28	NULL	NULL
BG32	NULL	NULL
BG33	NULL	NULL
BG38	NULL	NULL

Because my_other_table only contains ER19, this is the only value that gets mapped successfully, meaning the other values don't get mapped to null.

What I want to happen is this:

OLDID	MAPID	MAPVAL
DS12	NULL	DS12
ED18	NULL	ED18
ED19	ER19	ER04
BG28	NULL	BG28
BG32	NULL	BG32
BG33	NULL	BG33
BG38	NULL	BG38

where if there's no corresponding value in my_other_table, then my_table keeps it's original value.

What's a sensible way to achieve this, please?

Thanks for reading.

 

[gmmastros]

update my_table
set    sales_district_id = my_other_table.salesgroup
From   my_table
       inner join my_other_table
         on my_other_table.salesid = my_table.sales_id

Only the records that match will get updated. The rest will retain their original values.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hanchilicious]

O.M.G. How did I not see that? I've been drawing a blank on this for the past half hour with my brain not kicking into gear.

George, I thank you, my good man. If you were here, you'd be in danger of a drink or two. Have a star instead.

Thanks a lot.