• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

How to update the sql column with value from another table

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
TECH_NET
Asked:
TECH_NET
  • 3
  • 3
  • 3
1 Solution
 
TECH_NETAuthor Commented:
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
 
TECH_NETAuthor Commented:
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
 
Jaime OlivaresSoftware ArchitectCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jaime OlivaresSoftware ArchitectCommented:
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
 
imitchieCommented:
your 2nd SQL looks perfect to me. why do you need to change it?
0
 
Jaime OlivaresSoftware ArchitectCommented:
my first sql works with mysql and others,
the second sql works with ms sql
0
 
imitchieCommented:
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
 
TECH_NETAuthor Commented:
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
 
imitchieCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now