Avatar of Gordon Hughes
Gordon HughesFlag for United Kingdom of Great Britain and Northern Ireland asked on

Help with Update Query

What is wrong with this:-

PURREQH, COSTCTR
update PURREQH
set PURREQH.COSTCENTER = PURREQH.[DESTID]
where PURREQH.COSTCENTER is null
and PURREQH.DESTID in COSTCTR.COSTCENTER

get the following error message:-#

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'COSTCTR'.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Kyle Abrahams

update p
set p.CostCenter = p.DestID
from PURREQH p
join COSTCTR c on p.DESTID  = c.CostCenter
where p.CostCenter is null
David Kroll

The table names don't go at the top like that.  You should have:

update PURREQH
set PURREQH.COSTCENTER = PURREQH.[DESTID]
FROM PURREQH
where PURREQH.COSTCENTER is null
and PURREQH.DESTID in (SELECT COSTCENTER FROM COSTCTR)
ASKER
Gordon Hughes

Hi

Have tried dkrollCTN solution
The query runs but have an error as I did not take account of null
this is where there are nulls in the PURREQH.[DESTID]

so get the following:

Msg 515, Level 16, State 2, Procedure PURREQ_A, Line 87
Cannot insert the value NULL into column 'SESSIONID', table 'MP2LIVE.dbo.MP2TRIGGER'; column does not allow nulls. INSERT fails.
The statement has been terminated.

How do I change the query

Gordon
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
David Kroll

update PURREQH
set PURREQH.COSTCENTER = PURREQH.[DESTID]
FROM PURREQH
where PURREQH.COSTCENTER is null
and PURREQH.DESTID in (SELECT COSTCENTER FROM COSTCTR WHERE COSTCENTER IS NOT NULL)
ASKER
Gordon Hughes

Hi

Tried that, but still get

Msg 515, Level 16, State 2, Procedure PURREQ_A, Line 87
Cannot insert the value NULL into column 'SESSIONID', table 'MP2LIVE.dbo.MP2TRIGGER'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I think the issue is where the PURREQH.DESTID has a null value, but I may be wrong!

Gordon
David Kroll

Can you post the code from the MP2TRIGGER in table MP2LIVE?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kyle Abrahams

What if you just disabled the trigger?
ASKER
Gordon Hughes

Couple of things
Firstly I think i need to update the table PURREQ rather than the view PURREQH

so tried this
update PURREQ
set PURREQ.COSTCENTER = PURREQ.[DESTID]
FROM PURREQ
where PURREQ.COSTCENTER is null
and PURREQ.DESTID in (SELECT COSTCENTER FROM COSTCTR WHERE COSTCENTER IS NOT NULL)

but still get the same error message

MP2LIVE.dbo.MP2TRIGGER' is a table, cannot see any trigger code

Not sure what to do next, apart from give up today and go and have my dinner!!

Gordon
SOLUTION
David Kroll

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Gordon Hughes

Hi
How do I disable the trigger?
Gordon
Your help has saved me hundreds of hours of internet surfing.
fblack61
Kyle Abrahams

Actually the issue is here:
PURREQ_A

Is there that trigger on the table?
ASKER
Gordon Hughes

Hi
PURREQ_A is the trigger on PURREQ table
Do you know how I could temporary disable it?
Gordon
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Gordon Hughes

Hi
Will try the code, but when you right. Click there is not an option to disable the trigger

Gordon
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kyle Abrahams

You may not have permissions.

What account are you using?
ASKER
Gordon Hughes

Hi
Am using the sa account

Gordon
Kyle Abrahams

Are you sure you're clicking on the trigger?
disable
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Gordon Hughes

Hi
I used the disaable command, worked OK and then I could run

update PURREQ
set PURREQ.COSTCENTER = PURREQ.[DESTID]
FROM PURREQ
where PURREQ.COSTCENTER is null
and PURREQ.DESTID in (SELECT COSTCENTER FROM COSTCTR WHERE COSTCENTER IS NOT NULL)

It worked OK then enabled the trigger

Job Done
ASKER
Gordon Hughes

Was a little challenging, but got there with the help
Anthony Perkins

I used the disaable command, worked OK and then I could run
There are two problems with this approach:
1. Security:  The user must have ALTER permissions on the table .
2. Global scope:  Disabling a TRIGGER not only affects your session, but also affects all sessions for all users.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Gordon Hughes

Hi
I used it only to run an update script
I then ran the enable code
Is this ok?
Gordon
Anthony Perkins

So long as you are OK with the security (or lack of) and no one else is using that table at that time you should be fine.

Mind you those are big ifs.