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';
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;
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply