By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,105 Members | 1,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,105 IT Pros & Developers. It's quick & easy.

Update Query (Access vs. SQL Server)

P: n/a
Can someome please advise what the equivalent query would be in
Microsoft SQL Server ... I've tried a number of combinations with no
success ... Thanks, Ralph Noble (ra*********@hotmail.com)

================

UPDATE INVENTORY

INNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)

AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)

SET INVENTORY.DATE_PURCHASED = "20050127"
WHERE (((SALES.SOLD)="20050127"));

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use single quotes in SQL queries for delimited values. Double-quotes
have a different meaning.

UPDATE inventory
SET date_pruchased = '20050127'
FROM inventory i INNER JOIN sales s
on i.bar_code = s.bar_code and i.product_nbr = s.product_nbr
WHERE s.sold = '20050127'

Probably better could be:

UPDATE inventory
SET date_pruchased = s.sold
FROM inventory i INNER JOIN sales s
on i.bar_code = s.bar_code and i.product_nbr = s.product_nbr
WHERE s.sold = '20050127'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfl8xYechKqOuFEgEQJvPQCfUihJUy2SoOV6PB3sT2R8mo 6FVwMAoM2B
wvfAWGq5a8VVEgR7zuQef8uH
=UKJw
-----END PGP SIGNATURE-----
ra*********@hotmail.com wrote:
Can someome please advise what the equivalent query would be in
Microsoft SQL Server ... I've tried a number of combinations with no
success ... Thanks, Ralph Noble (ra*********@hotmail.com)

================

UPDATE INVENTORY

INNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)

AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)

SET INVENTORY.DATE_PURCHASED = "20050127"
WHERE (((SALES.SOLD)="20050127"));

Nov 13 '05 #2

P: n/a
ra*********@hotmail.com wrote:
Can someome please advise what the equivalent query would be in
Microsoft SQL Server ... I've tried a number of combinations with no
success ... Thanks, Ralph Noble (ra*********@hotmail.com)

================

UPDATE INVENTORY

INNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)

AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)

SET INVENTORY.DATE_PURCHASED = "20050127"
WHERE (((SALES.SOLD)="20050127"));


SQL Server won't update on a join.

update inventory
SET INVENTORY.DATE_PURCHASED = '20050127'
where exists(select * from sales where sales.product_nbr =
inventory.product_nbr and sales.sold='20050127')
--
This sig left intentionally blank
Nov 13 '05 #3

P: n/a
Trevor Best wrote:
SQL Server won't update on a join.

update inventory
SET INVENTORY.DATE_PURCHASED = '20050127'
where exists(select * from sales where sales.product_nbr =
inventory.product_nbr and sales.sold='20050127')


ALTER PROCEDURE UPDATESchoolSubjectIDGlobal
@SchoolID int,
@SubjectID int
AS
UPDATE
t
SET
t.SubjectID = @SubjectID
FROM
TimeTable t
JOIN
SchoolTeachers st
ON
t.TeacherID = st.TeacherID
WHERE
st.SchoolID = @SchoolID
RETURN
Nov 13 '05 #4

P: n/a
Trevor Best <no****@besty.org.uk> wrote in news:41f9f700$0$26019
$f*******@news.zen.co.uk:
ra*********@hotmail.com wrote:
Can someome please advise what the equivalent query would be in
Microsoft SQL Server ... I've tried a number of combinations with no
success ... Thanks, Ralph Noble (ra*********@hotmail.com)

================

UPDATE INVENTORY

INNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)

AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)

SET INVENTORY.DATE_PURCHASED = "20050127"
WHERE (((SALES.SOLD)="20050127"));


SQL Server won't update on a join.

update inventory
SET INVENTORY.DATE_PURCHASED = '20050127'
where exists(select * from sales where sales.product_nbr =
inventory.product_nbr and sales.sold='20050127')


ALTER PROCEDURE UPDATESchoolSubjectIDGlobal
@SchoolID int,
@SubjectID int
AS
UPDATE
t
SET
t.SubjectID = @SubjectID
FROM
TimeTable t
JOIN
SchoolTeachers st
ON
t.TeacherID = st.TeacherID
WHERE
st.SchoolID = @SchoolID
RETURN

Please, accept my apologies if I have posted this twice.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
Nov 13 '05 #5

P: n/a
Lyle Fairfield wrote:
Trevor Best <no****@besty.org.uk> wrote in news:41f9f700$0$26019
$f*******@news.zen.co.uk:

ra*********@hotmail.com wrote:
Can someome please advise what the equivalent query would be in
Microsoft SQL Server ... I've tried a number of combinations with no
success ... Thanks, Ralph Noble (ra*********@hotmail.com)

================

UPDATE INVENTORY

INNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)

AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)

SET INVENTORY.DATE_PURCHASED = "20050127"
WHERE (((SALES.SOLD)="20050127"));


SQL Server won't update on a join.

update inventory
SET INVENTORY.DATE_PURCHASED = '20050127'
where exists(select * from sales where sales.product_nbr =
inventory.product_nbr and sales.sold='20050127')

ALTER PROCEDURE UPDATESchoolSubjectIDGlobal
@SchoolID int,
@SubjectID int
AS
UPDATE
t
SET
t.SubjectID = @SubjectID
FROM
TimeTable t
JOIN
SchoolTeachers st
ON
t.TeacherID = st.TeacherID
WHERE
st.SchoolID = @SchoolID
RETURN

Please, accept my apologies if I have posted this twice.


I was unaware of that syntax.

--
This sig left intentionally blank
Nov 13 '05 #6

P: n/a
SQL Server will update on a join, but the query designer window in
Enterprise Manager doesn't like that syntax/won' t represent it
graphically. It is also a little different from Access sql. It will
complain if you feed it an update with a join by graying out the the
diagram and grid panes, but it will execute just fine.

UPDATE INVENTORY
SET DATE_PURCHASED = '1/27/2005'
FROM INVENTORY, SALES WHERE (INVENTORY.BAR_CODE = SALES.BAR_CODE)
AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR) AND (SALES.SOLD =
'1/27/2005')

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.