Updating One table Based On Another with Like

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating One table Based On Another with Like

Author

Topic

M374llic4
Starting Member

4 Posts

Posted - 2005-08-22 : 04:30:57

Hello Everyone,

I am trying to update one table called categories, the column catagories_image from the table called catagories_description where the column catagories_name is "something"

Basicly, I have named a catagory something, and an image the same thing, Based on the catagory name, I need the field "catagories_image" to change based on the catagories name

Here is the code I have that is not working, I have done alot of searching and see alot about JOIN and such, but am not sure its what i need

 
update categories 
set categories_image = 'accessories.jpg' 
from categories_description 
WHERE categories_name like 'Accessories'

As you can see. Any field with the name accessories in the table categories_description, I want the catagories_image in the table catagories to change to accessories.jpg.

I did it in the same table based on the catagories_id, but there are over 100, different numbers. And it would take forever to put them all in a single statement. And there are over 50 total catagories I will need to do this to.

If anyone could help out, It would really help me out!!
Thanks in advance, and I really appreciate it!
Dan Termani
http://TermaniMotorsports.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-22 : 05:15:50

Try this (before that take back of both the tables and if the updates are wrong restore them)

Update C set C.categories_image = 'accessories.jpg'
from categories C inner join categories_description D
on C.category_id=D.category_id WHERE C.categories_name like '%Accessories%'

Othertwise post the table structures of categories and description_description

Madhivanan

Failing to plan is Planning to fail

M374llic4
Starting Member

4 Posts

Posted - 2005-08-22 : 13:47:39

It gave an error as usual #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'from categories C inner join categories_description D
on C.cat
This is the table categories_description


 categories_id  int(11)   No  0                
   language_id  int(11)   No  1                
   categories_name  varchar(32)   No

And this is catagories


 categories_id  int(11)   No    auto_increment              
   categories_image  varchar(64)   Yes  NULL                
   parent_id  int(11)   No  0                
   sort_order  int(3)   Yes  NULL                
   date_added  datetime   Yes  NULL                
   last_modified  datetime   Yes  NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-22 : 13:49:42

quote:
Originally posted by M374llic4

It gave an error as usual #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'from categories C inner join categories_description D
on C.cat

This is a Microsoft SQL Server forum. You might have better luck on a MySql forum such as the one over at dbforums.com.

Tara

M374llic4
Starting Member

4 Posts

Posted - 2005-08-22 : 13:51:14

oops, damn, sorry. I didnt even notice!

M374llic4
Starting Member

4 Posts

Posted - 2005-08-23 : 17:14:42

Just incase anyone wants to know what the end result was. I finally got it


UPDATE categories ,categories_description
SET categories.categories_image= 'wheels.jpg'
WHERE categories_description.categories_name like 'wheels' 
and categories.categories_id =categories_description.categories_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources