Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Cant get this SQL to work !
Wed, Jan 11 2017 9:31 AMPermanent Link

kamran

Hi

so i have a product table which i want to populate with a field from the category table.

My Code is:

UPDATE
 Product
SET
 Product.product_section = SELECT category.category_two FROM category
                            WHERE Product.category_id = category.category_id)
WHERE EXISTS
 (SELECT * FROM category
    WHERE
       Product.category_id = category.category_id);

The code should work... but does not.!!!.. it looks right !! it fails on line 4 ( just after the SELECT )

perhaps the dbisam sql format is different to edb sql format in some way?

How to achieve the result needed?

Thanks

Kamran
Wed, Jan 11 2017 10:05 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

kamran


>perhaps the dbisam sql format is different to edb sql format in some way?

DBISAM and ElevateDB have implemented differing versions of the SQL standard so yes the syntax is different. I think you'll need to use a JOIN. I can't bring it to mind so I'll have a look at my old code tonight and if no-one else gets back to you I'll post in the morning.

Roy
Wed, Jan 11 2017 1:38 PMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Kamran,

As Roy already said, DBISAM and EDB have different SQL syntax.
DBISAM is based on SQL-92 and EDB on SQL 2003.

As far as I could understand from your code, this might be what you need:
 
UPDATE
  Product
SET
  Product.product_section = category.category_two
FROM
  Product
  LEFT OUTER JOIN Category ON Category.category_id = Product.category_id
WHERE
  Category.category_id IS NOT NULL


--
Fernando Dias
[Team Elevate]
Thu, Jan 12 2017 6:46 AMPermanent Link

kamran

Hi Fernando

Works Perfect !!!

Thank you

Fernando Dias wrote:

Kamran,

As Roy already said, DBISAM and EDB have different SQL syntax.
DBISAM is based on SQL-92 and EDB on SQL 2003.

As far as I could understand from your code, this might be what you need:
 
UPDATE
  Product
SET
  Product.product_section = category.category_two
FROM
  Product
  LEFT OUTER JOIN Category ON Category.category_id = Product.category_id
WHERE
  Category.category_id IS NOT NULL


--
Fernando Dias
[Team Elevate]
Image