Updating a table from another table or a query.

  • I have a procedure that creates a table.  One of the fields labeled delay is yes or no field.   I have two queries that i can either create two tables and use a field from there with a value of > 0 to put yes into delay field of the other table.  I'm at loss as to best way to do this.

    I attached the query to create the original table in case that helps or is needed.

    Based on the below query if apdelay > 0 then tbl_salesactivity.delay = 'yes' where tbl_salesactivity_new.shipment_id = apj.shipment

    SELECT

    apj.shipmentid,

    sr.first_completed_date as FirstComplete,

    date(apj.datesent) as AccrlDate,

    sum(APJ.theamount) As AccrlAmt,

    sum(if(date(aPj.create_date)>date(first_completed_date),1,0)) as APDelay

    FROM

    cs.acct_apaccrual_journal apj

    JOIN cs.shipment_reporting SR ON APJ.SHIPMENTID=SR.SHIPMENT_ID

     

    Where apj.datesent is not null and  if(date(aPj.create_date)>date(first_completed_date),1,0) > 0 -- second where clause is just for testing

     

    GROUP BY

    aPj.shipmentid,

    date(aPj.datesent)

     

     

    Attachments:
    You must be logged in to view attached files.
  • I tried the below but syntax is not correct.

     

    update tbl_salesactivity_new

    set

    tbl_salesactivity_new.delay = 'Yes'

    FROM

    arh ar

    inner join

    tbl_salesactivity_new sn

    on sn.shipment = ar.shipment_id

    where arh.ardelay > 0

  • The syntax is incorrect because you have aliased both tables used in the query, but then still reference the original table names everywhere except in the join between sn and sr:

    update tbl_salesactivity_new

    set

    tbl_salesactivity_new.delay = 'Yes'

    FROM

    arh ar

    inner join

    tbl_salesactivity_new sn

    on sn.shipment = ar.shipment_id

    where arh.ardelay > 0

    Note: The update statement of the original table might happen if you fixed the other aliases, but might update every row since you are not tying the update to the join or where clause. It's important to use aliases consistently if you use them. 

    I recommend getting in the habit of including the schema when referencing tables, views, procedures, etc., even if it is dbo.

    Most volunteers don't like to open documents like Excel or Word documents which could contain dangerous scripts/code. See the link for what & how to post information to help others help you.

    How to post code problems

     

     

  • Thank you and i appreciate you letting me know about attaching a doc.  My apologies and i will remember in the future.

    Did you mean like the below?

    update sn

    set

    sn.delay = 'Yes'

    FROM

    arh ar

    inner join

    tbl_salesactivity_new sn

    on sn.shipment = ar.shipment_id

    where ar.ardelay > 0

  • Does it work?

    Yes, that is using aliases everywhere the tables are referenced.

  • I get an error From is not valid at this position expecting eof

  • UPDATE tbl_salesactivity_new sn

    INNER JOIN arh ar

    ON sn.shipment = ar.shipment_i

    AND ar.ardelay > '0'

    SET sn.delay = 'Yes'

     

  • chaunnahrailey@gmail.com wrote:

    UPDATE tbl_salesactivity_new sn INNER JOIN arh ar ON sn.shipment = ar.shipment_i AND ar.ardelay > '0' SET sn.delay = 'Yes'

    That is almost correct. Just need to shuffle things round a bit:

    UPDATE sn
    SET delay = 'Yes'
    FROM tbl_salesactivity_new sn
    INNER JOIN arh ar
    ON sn.shipment = ar.shipment_i
    AND ar.ardelay > '0';

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I have one more issue just brought to my attention.  If the apdelay = 0 then No needs to be put into the delay field.

     

     

    UPDATE tbl_salesactivity sn

    INNER JOIN apj aj

    ON sn.shipment = aj.shipmentid

    AND aj.APDelay > '0'

    SET sn.delay = 'Yes'

  • You did not respond to my previous post. Did my query work how you intended? Some feedback would have been polite.

    But if it did, this version may give you what you need. Please post back with your findings.

    UPDATE sn
    SET delay = IIF(aj.apdelay = 0,'No','Yes')
    FROM tbl_salesactivity_new sn
    JOIN arh ar
    ON sn.shipment = ar.shipment_i
    AND ar.ardelay > '0'
    JOIN apj aj
    ON sn.shipment = aj.shipmentid;

    • This reply was modified 1 month, 1 week ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply