Solved

Help with Update Query

Posted on 2014-03-03
22
174 Views
Last Modified: 2014-03-18
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
Comment
Question by:GiaHughes
  • 10
  • 6
  • 4
  • +1
22 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39900890
update p
set p.CostCenter = p.DestID
from PURREQH p
join COSTCTR c on p.DESTID  = c.CostCenter
where p.CostCenter is null
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39900896
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
 

Author Comment

by:GiaHughes
ID: 39900927
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
 
LVL 11

Expert Comment

by:David Kroll
ID: 39900933
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
 

Author Comment

by:GiaHughes
ID: 39900948
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
 
LVL 11

Expert Comment

by:David Kroll
ID: 39900964
Can you post the code from the MP2TRIGGER in table MP2LIVE?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39900971
What if you just disabled the trigger?
0
 

Author Comment

by:GiaHughes
ID: 39901016
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
 
LVL 11

Assisted Solution

by:David Kroll
David Kroll earned 100 total points
ID: 39901023
The error you're getting is coming from MP2TRIGGER.  We need to see what's going on in there.
0
 

Author Comment

by:GiaHughes
ID: 39928833
Hi
How do I disable the trigger?
Gordon
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39929221
Actually the issue is here:
PURREQ_A

Is there that trigger on the table?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:GiaHughes
ID: 39933902
Hi
PURREQ_A is the trigger on PURREQ table
Do you know how I could temporary disable it?
Gordon
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 400 total points
ID: 39934456
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
 

Author Comment

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

Gordon
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39935639
You may not have permissions.

What account are you using?
0
 

Author Comment

by:GiaHughes
ID: 39935665
Hi
Am using the sa account

Gordon
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39937907
Are you sure you're clicking on the trigger?
disable
0
 

Author Comment

by:GiaHughes
ID: 39938141
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
 

Author Closing Comment

by:GiaHughes
ID: 39938144
Was a little challenging, but got there with the help
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39938499
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
 

Author Comment

by:GiaHughes
ID: 39938571
Hi
I used it only to run an update script
I then ran the enable code
Is this ok?
Gordon
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39938635
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now