Solved

How to update the sql column with value from another table

Posted on 2007-12-01
9
245 Views
Last Modified: 2012-05-05
I have a table NOTIFYDEALS_CJ_PRODUCT_CATEGORY that contains list of categories defined by column NAME.

Now i wish to update a transaction table NOTIFYDEALS_CJ_ADVERTISER_LIST's column CATEGORY_ID with the value retrieved from NOTIFYDEALS_CJ_PRODUCT_CATEGORY . The column name is KEYWORD_ID


Here is a sample
NOTIFYDEALS_CJ_PRODUCT_CATEGORY
===================================
KEYWORD_ID, NAME
1,'Accessories'
2 ,'Astrology'
3,'Auction'

NOTIFYDEALS_CJ_ADVERTISER_LIST's
====================================
CATEGORY,CATEGORY_ID
'Accessories',NULL
'Astrology',NULL
'Auction',NULL

The join column is CATEGORY of NOTIFYDEALS_CJ_ADVERTISER_LIST's  with NAME column of NOTIFYDEALS_CJ_PRODUCT_CATEGORY




0
Comment
Question by:TECH_NET
  • 3
  • 3
  • 3
9 Comments
 

Author Comment

by:TECH_NET
ID: 20390354
I am using the following script

UPDATE NOTIFYDEALS_CJ_ADVERTISER_LIST
SET CATEGORY_ID=(SELECT KEYWORD_ID FROM NOTIFYDEALS_CJ_PRODUCT_CATEGORY WHERE
NOTIFYDEALS_CJ_ADVERTISER_LIST.CATEGORY=NOTIFYDEALS_CJ_PRODUCT_CATEGORY.CATEGORY
0
 

Author Comment

by:TECH_NET
ID: 20390358
UPDATE NOTIFYDEALS_CJ_ADVERTISER_LIST
SET CATEGORY_ID =(SELECT KEYWORD_ID FROM NOTIFYDEALS_CJ_PRODUCT_CATEGORY WHERE
NOTIFYDEALS_CJ_ADVERTISER_LIST.CATEGORY=NOTIFYDEALS_CJ_PRODUCT_CATEGORY.NAME)
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20390492
try with:

UPDATE
      NOTIFYDEALS_CJ_ADVERTISER_LIST, NOTIFYDEALS_CJ_PRODUCT_CATEGORY
SET
      NOTIFYDEALS_CJ_ADVERTISER_LIST.CATEGORY_ID =  NOTIFYDEALS_CJ_PRODUCT_CATEGORY.KEYWORD_ID
WHERE
      NOTIFYDEALS_CJ_ADVERTISER_LIST.CATEGORY = NOTIFYDEALS_CJ_PRODUCT_CATEGORY.NAME
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20390504
Hmm, I think this won't work with T-SQL but work with other SQLs.
Better you try with:

UPDATE
      NOTIFYDEALS_CJ_ADVERTISER_LIST
SET
      NOTIFYDEALS_CJ_ADVERTISER_LIST.CATEGORY_ID =  NOTIFYDEALS_CJ_PRODUCT_CATEGORY.KEYWORD_ID
FROM
      NOTIFYDEALS_CJ_ADVERTISER_LIST
INNER JOIN
      NOTIFYDEALS_CJ_PRODUCT_CATEGORY
ON
      NOTIFYDEALS_CJ_ADVERTISER_LIST.CATEGORY = NOTIFYDEALS_CJ_PRODUCT_CATEGORY.NAME

Also would be incredibly helpful if you reduce the names of your tables, even can affect the performance of your database transactions
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 25

Expert Comment

by:imitchie
ID: 20390594
your 2nd SQL looks perfect to me. why do you need to change it?
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20391057
my first sql works with mysql and others,
the second sql works with ms sql
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20392326
In case anyone's confused, I was commenting on the question itself. The SQL looks perfect for SQL Server (2005), which is where this question is. What was the question?
0
 

Author Comment

by:TECH_NET
ID: 20393284
I am sure what is the issue with my second sql, I changed it to the following and it worked.

UPDATE NOTIFYDEALS_CJ_ADVERTISER_LIST
SET CATEGORY_ID =(SELECT KEYWORD_ID FROM NOTIFYDEALS_CJ_PRODUCT_CATEGORY  PROD_CAT WHERE
NOTIFYDEALS_CJ_ADVERTISER_LIST.CATEGORY=PROD_CAT .NAME)
0
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20393295
That's exactly the same as your 2nd comment, except you aliased the inner table name... which shouldn't make any difference. Maybe there's a misspelling in the long table name on the last line (2nd comment)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now