• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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'.
0
GiaHughes
Asked:
GiaHughes
  • 10
  • 6
  • 4
  • +1
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
update p
set p.CostCenter = p.DestID
from PURREQH p
join COSTCTR c on p.DESTID  = c.CostCenter
where p.CostCenter is null
0
 
David KrollCommented:
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)
0
 
GiaHughesAuthor Commented:
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
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
David KrollCommented:
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)
0
 
GiaHughesAuthor Commented:
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
0
 
David KrollCommented:
Can you post the code from the MP2TRIGGER in table MP2LIVE?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
What if you just disabled the trigger?
0
 
GiaHughesAuthor Commented:
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
0
 
David KrollCommented:
The error you're getting is coming from MP2TRIGGER.  We need to see what's going on in there.
0
 
GiaHughesAuthor Commented:
Hi
How do I disable the trigger?
Gordon
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Actually the issue is here:
PURREQ_A

Is there that trigger on the table?
0
 
GiaHughesAuthor Commented:
Hi
PURREQ_A is the trigger on PURREQ table
Do you know how I could temporary disable it?
Gordon
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
disable trigger dbo.PURREQ_A on Purreq

-- code

enable trigger dbo.Purreq_A on purreq


or
you can just right click and disabled the trigger manually.

be sure to enable it when you're done.
0
 
GiaHughesAuthor Commented:
Hi
Will try the code, but when you right. Click there is not an option to disable the trigger

Gordon
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You may not have permissions.

What account are you using?
0
 
GiaHughesAuthor Commented:
Hi
Am using the sa account

Gordon
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Are you sure you're clicking on the trigger?
disable
0
 
GiaHughesAuthor Commented:
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
0
 
GiaHughesAuthor Commented:
Was a little challenging, but got there with the help
0
 
Anthony PerkinsCommented:
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.
0
 
GiaHughesAuthor Commented:
Hi
I used it only to run an update script
I then ran the enable code
Is this ok?
Gordon
0
 
Anthony PerkinsCommented:
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now