<div>
update p<br />
set p.CostCenter = p.DestID<br />
from PURREQH p<br />
join COSTCTR c on p.DESTID &nbsp;= c.CostCenter<br />
where p.CostCenter is null
</div>


<div>
The table names don't go at the top like that. &nbsp;You should have:<br />
<br />
update PURREQH<br />
set PURREQH.COSTCENTER = PURREQH.[DESTID]<br />
FROM PURREQH<br />
where PURREQH.COSTCENTER is null<br />
and PURREQH.DESTID in (SELECT COSTCENTER FROM COSTCTR)
</div>


<div>
Hi<br />
<br />
Have tried dkrollCTN solution<br />
The query runs but have an error as I did not take account of null<br />
this is where there are nulls in the PURREQH.[DESTID]<br />
<br />
so get the following:<br />
<br />
Msg 515, Level 16, State 2, Procedure PURREQ_A, Line 87<br />
Cannot insert the value NULL into column 'SESSIONID', table 'MP2LIVE.dbo.MP2TRIGGER'; column does not allow nulls. INSERT fails.<br />
The statement has been terminated.<br />
<br />
How do I change the query<br />
<br />
Gordon
</div>


<div>
update PURREQH<br />
set PURREQH.COSTCENTER = PURREQH.[DESTID]<br />
FROM PURREQH<br />
where PURREQH.COSTCENTER is null<br />
and PURREQH.DESTID in (SELECT COSTCENTER FROM COSTCTR WHERE COSTCENTER IS NOT NULL)
</div>


<div>
Hi<br />
<br />
Tried that, but still get<br />
<br />
Msg 515, Level 16, State 2, Procedure PURREQ_A, Line 87<br />
Cannot insert the value NULL into column 'SESSIONID', table 'MP2LIVE.dbo.MP2TRIGGER'; column does not allow nulls. INSERT fails.<br />
The statement has been terminated.<br />
<br />
I think the issue is where the PURREQH.DESTID has a null value, but I may be wrong!<br />
<br />
Gordon
</div>


<div>
Can you post the code from the MP2TRIGGER in table MP2LIVE?
</div>


<div>
What if you just disabled the trigger?
</div>


<div>
Couple of things<br />
Firstly I think i need to update the table PURREQ rather than the view PURREQH<br />
<br />
so tried this<br />
update PURREQ<br />
set PURREQ.COSTCENTER = PURREQ.[DESTID]<br />
FROM PURREQ<br />
where PURREQ.COSTCENTER is null<br />
and PURREQ.DESTID in (SELECT COSTCENTER FROM COSTCTR WHERE COSTCENTER IS NOT NULL) <br />
<br />
but still get the same error message<br />
<br />
MP2LIVE.dbo.MP2TRIGGER' is a table, cannot see any trigger code<br />
<br />
Not sure what to do next, apart from give up today and go and have my dinner!!<br />
<br />
Gordon
</div>


<div>
Hi<br />
How do I disable the trigger?<br />
Gordon
</div>


<div>
Actually the issue is here:<br />
PURREQ_A<br />
<br />
Is there that trigger on the table?
</div>


<div>
Hi<br />
PURREQ_A is the trigger on PURREQ table<br />
Do you know how I could temporary disable it?<br />
Gordon
</div>


<div>
Hi<br />
Will try the code, but when you right. Click there is not an option to disable the trigger<br />
<br />
Gordon
</div>


<div>
You may not have permissions.<br />
<br />
What account are you using?
</div>


<div>
Hi<br />
Am using the sa account<br />
<br />
Gordon
</div>


<div>
Are you sure you're clicking on the trigger?<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/03_w12/840570/disable.PNG" target="_blank" title="disable (5 KB)"><img alt="disable" class="file-block lazyload" style="max-width: 351px;" data-src="https://filedb.experts-exchange.com/incoming/2014/03_w12/840570/disable.PNG" /></a>
</div>


disable.PNG

<div>
Hi<br />
I used the disaable command, worked OK and then I could run<br />
<br />
update PURREQ<br />
set PURREQ.COSTCENTER = PURREQ.[DESTID]<br />
FROM PURREQ<br />
where PURREQ.COSTCENTER is null<br />
and PURREQ.DESTID in (SELECT COSTCENTER FROM COSTCTR WHERE COSTCENTER IS NOT NULL) <br />
<br />
It worked OK then enabled the trigger<br />
<br />
Job Done
</div>


<div>
Was a little challenging, but got there with the help
</div>


<div>
<i>I used the disaable command, worked OK and then I could run</i><br />
There are two problems with this approach:<br />
1. Security: &nbsp;The user must have ALTER permissions on the table .<br />
2. Global scope: &nbsp;Disabling a TRIGGER not only affects your session, but also affects all sessions for all users.
</div>


<div>
Hi<br />
I used it only to run an update script<br />
I then ran the enable code<br />
Is this ok?<br />
Gordon
</div>


<div>
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.<br />
<br />
Mind you those are big ifs.
</div>


<div>
<div class="content wysiwyg-content">
What is wrong with this:-<br />
<br />
PURREQH, COSTCTR<br />
update PURREQH<br />
set PURREQH.COSTCENTER = PURREQH.[DESTID]<br />
where PURREQH.COSTCENTER is null<br />
and PURREQH.DESTID in COSTCTR.COSTCENTER<br />
<br />
get the following error message:-#<br />
<br />
Msg 102, Level 15, State 1, Line 1<br />
Incorrect syntax near ','.<br />
Msg 102, Level 15, State 1, Line 5<br />
Incorrect syntax near 'COSTCTR'.
</div>
</div>


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'.

Help with Update Query

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.