Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

Assistance with an update statement that requires inner join

I am trying to use the update statement below. I receive an error. I am sure it's syntax.  

Here is the query as I originally run it:
UPDATE  a
SET a.startdate=b.proposed_start_date, 
        a.enddate=b.proposed_end_date
FROM    njobslstasks a INNER JOIN 
        lot_schedule_proposed_changes b ON a.PROGRESS_RECID_IDENT_ = b.progress_id
WHERE   b.bldrcode = 'jima'

Open in new window


Here is how the management studio inside of the query window modifies it:

UPDATE    a
SET              a.startdate = b.proposed_start_date, a.enddate = b.proposed_end_date
FROM         rems.njobslstasks AS a INNER JOIN
                      rems.lot_Schedule_proposed_changes AS b ON a.PROGRESS_RECID_IDENT_ = b.progress_id CROSS JOIN
                      a
WHERE     (b.bldrcode = 'jima')

Open in new window


I attached the error message as a screenshot
error.png
0
J C
Asked:
J C
  • 2
1 Solution
 
Paul JacksonCommented:
do you get the error if you take out the 'CROSSJOIN a' ?
I don't know why it is being added but it seems to be in error.
Make sure you are using New Query option not Open Table option.

Same sort of problem here :

http://www.experts-exchange.com/Programming/Languages/.NET/Q_22640924.html
0
 
J CAuthor Commented:
jacko72,

Arg, I was not using the new query window and once I did it works. Can you tell me what I'd need to do to add the delete statement so that after the statement runs it deletes the records in that table? Thank you for your help
0
 
Paul JacksonCommented:
I think this will do it, please test before using :
UPDATE  a
SET a.startdate=b.proposed_start_date, 
        a.enddate=b.proposed_end_date
FROM    njobslstasks a INNER JOIN 
        lot_schedule_proposed_changes b ON a.PROGRESS_RECID_IDENT_ = b.progress_id
WHERE   b.bldrcode = 'jima' 
go
Delete b FROM njobslstasks a INNER JOIN
        lot_schedule_proposed_changes b ON a.PROGRESS_RECID_IDENT_ = b.progress_id
WHERE   b.bldrcode = 'jima' 
go

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now