Update Query (Access vs. SQL Server)

[ralph_noble]

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"));

[MGFoster]

-----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"));

[Trevor Best]

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

[Lyle Fairfield]

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

[Lyle Fairfield]

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.

[Trevor Best]

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

[Thor_MN]

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')