Missing something here 1

[elentz]

I am trying to update a table with info from another table and also set two fields with the same info for each field. The first field productid will need to be inserted into the producttaxrel table with the contents of the same field in the products table.

Here's my query:

Update communiq_test.vtiger_producttaxrel T, communiq_test.vtiger_products P SET T.productid = P.productid,T.taxid=2, T.taxpercentage=6


The query comes back with 0 rows affected.

Comments as to why this won't work?

Thanks

 

[r937]

if you're trying to SET T.productid = P.productid, you're not joining on those columns

may i ask how the rows should be joined in order to update the correct rows?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[elentz]

actually would it be just as easy if producttaxrel table was empty, then an insert used? The producttaxrel table is used to indicate within my system which productid would be taxable using the other two fields. the common fields would be productid in any case

Thanks

 

[r937]

if "the common fields would be productid in any case" then why are you trying to set them equal? and if they're supposed to be equal, then what else is there that has to be set from one table into the other? the only other column updates are constants, T.taxid=2, T.taxpercentage=6

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[elentz]

Here's the problem I am trying to deal with. Normally when I use my CRM system to add a new product to my inventory I get the option to make it taxable. This is reflected in the producttaxrel table with the new productid, taxid, and taxpercentage entered. This works fine when I just add things manually. There is a way to import new products (many at a time) into the inventory, but, that method does not make new entries in the producttaxrel table. What I am trying to do is use a query to add in the new productid's and set the taxid and the taxpercentage to 2 & 6 respectively. I know the ultimate fix is to get the developers to fix the code, but that isn't going to happen, at least they don't seem to care about this issue. So I want to use a query to make this happen. And that is why I am here asking for help.

Sorry for any confusion, and thanks for any assistance

 

[r937]

sounds like you are looking for "insert where not exists" rather than "update"

INSERT
  INTO communiq_test.vtiger_producttaxrel
     ( productid
     , taxid
     , taxpercentage )
SELECT p.productid
     , 2 -- taxid
     , 6 -- taxpercentage
  FROM communiq_test.vtiger_products AS p
LEFT OUTER
  JOIN communiq_test.vtiger_producttaxrel AS pt
    ON pt.productid = p.productid
 WHERE pt.productid IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[elentz]

That did exactly what I needed. thank you very much!