July 1, 2022 at 4:02 pm
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)
July 1, 2022 at 4:28 pm
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
July 1, 2022 at 5:14 pm
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.
July 1, 2022 at 5:54 pm
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
July 1, 2022 at 6:21 pm
Does it work?
Yes, that is using aliases everywhere the tables are referenced.
July 1, 2022 at 6:28 pm
I get an error From is not valid at this position expecting eof
July 1, 2022 at 6:49 pm
UPDATE tbl_salesactivity_new sn
INNER JOIN arh ar
ON sn.shipment = ar.shipment_i
AND ar.ardelay > '0'
SET sn.delay = 'Yes'
July 3, 2022 at 9:16 am
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.
July 6, 2022 at 2:51 pm
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'
July 7, 2022 at 9:24 am
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;
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply