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

Sql update statement

I have the following sql update statement that I need to run.  

I am not sure this is written correctly and was looking for some help.  

update binlocat
set LICENSE_PLATE = UPLOAD.FIELD034
where SUBSTRING(BINLOCAT.EXTENDED, 102, 8) = UPLOAD.FIELD021 and
                  (UPLOAD.FIELD001='st') AND (UPLOAD.FIELD003='05') AND
                   (UPLOAD.FIELD008 like '10pl%')
0
notasgoodasyou
Asked:
notasgoodasyou
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Since this UPDATE statement has two tables, binlocal and UPLOAD, then it needs a FROM and JOIN that spell out how the two tables are related.  Something like (replace id with the column that joins these two tables)...
update binlocat
set LICENSE_PLATE = UPLOAD.FIELD034
FROM binlocat
   JOIN UPLOAD ON binlocat.id = UPLOAD.id
where SUBSTRING(BINLOCAT.EXTENDED, 102, 8) = UPLOAD.FIELD021 and 
   (UPLOAD.FIELD001='st') AND 
   (UPLOAD.FIELD003='05') AND
   (UPLOAD.FIELD008 like '10pl%')

Open in new window

0
 
notasgoodasyouAuthor Commented:
Thanks Jim

Will the following work?


update binlocat
set LICENSE_PLATE = UPLOAD.FIELD034
FROM binlocat
   JOIN UPLOAD ON SUBSTRING(BINLOCAT.EXTENDED, 102, 8) = UPLOAD.FIELD021
where SUBSTRING(BINLOCAT.EXTENDED, 102, 8) = UPLOAD.FIELD021 and
   (UPLOAD.FIELD001='st') AND
   (UPLOAD.FIELD003='05') AND
   (UPLOAD.FIELD008 like '10pl%')
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Should work, although if you are joining on the SUBSTRING.. expression then you don't need the same expression in the WHERE clause.  

I don't have a connection to your data source, so I can't check for things like table and column names spelled correctly, data types, etc.

Check out the T-SQL below, and note the use of table aliases (b and u), indenting, and removed some unnecessary parentheses to make it much more readable.
update b
set b.LICENSE_PLATE = u.FIELD034
FROM binlocat b
   JOIN UPLOAD u ON SUBSTRING(b.EXTENDED, 102, 8) = u.FIELD021 
where
   u.FIELD001='st' AND 
   u.FIELD003='05' AND
   u.FIELD008 like '10pl%'

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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