August 22, 2016 at 1:36 am
I am working with SQL Server 2008. I have 2 tables here.
shades_table
color_name category location
Aqua Blue House A
Denim Blue House B
Azure Blue House B
Zaffre Blue House A
Scarlet Red House C
Ruby Red House D
Chili Red House D
Crimson Red House A
objects_table
object_name color_cat location2
Super Aqua shoes Null Null
Azure wallet Null Null
Awesome Scarlet chair Null Null
Ruby socks Null Null
Sparkly Denim chalk Null Null
Chili paper Null Null
Zaffre vase Null Null
Sharp Crimson eraser Null Null
Crimson watch Null Null
Excellent Scarlet bucket Null Null
I have a reference table called shades_table. A column contains color_name, category of the color and location.
I am working on the objects_table. The object_name column contains the color name. I would want a SQL to auto match the color_names, location to the Object_name, and then update the Null values.
My current method is to export the shades_table to a csv file. Then I wrote a Java program which reads the csv file, and output to a sql file to be run by the server:
UPDATE objects_table SET color_cat='Blue', location2 = 'House A' WHERE object_name LIKE '%Aqua%' AND color_cat IS NULL
UPDATE objects_table SET color_cat='Blue', location2 = 'House B' WHERE object_name LIKE '%Denim%' AND color_cat IS NULL
UPDATE objects_table SET color_cat='Blue', location2 = 'House B' WHERE object_name LIKE '%Azure%' AND color_cat IS NULL
etc...
This method works, but it's stupid.
I just don't know how to write the SQL statements which can do what I want.
Update:
If i were to update a single column, the below would work.
UPDATE a
SET a.color_cat=b.category
FROM objects_table a
JOIN shades_table b ON a.[object_name] LIKE '%'+b.[color_name]+'%'
WHERE a.color_cat IS null
But I am updating 2 coloumns at once, 2 SET statements under UPDATE won't work and gives 'Incorrect syntax near the keyword 'set'.
what sql can do?
August 22, 2016 at 1:54 am
theres no limit to the number of columns you can update in one go.
UPDATE a
SET a.color_cat = b.category
, a.location = b.location2
FROM objects_table a
JOIN shades_table b
ON a.[object_name] LIKE '%'+b.[color_name]+'%'
WHERE a.color_cat IS null
August 22, 2016 at 3:01 am
Thanks, frederico_fonseca, you helped me.
I was really stressed out because my boss just keep pressuring me, and the fact that I am not very good at SQL but more of a Network Admin person.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply