Update Query (Access vs. SQL Server)

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 (ralph_noble@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"));

re: Update Query (Access vs. SQL Server)

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


ralph_noble@hotmail.com wrote:[color=blue]
> 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 (ralph_noble@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"));
>[/color]

re: Update Query (Access vs. SQL Server)

ralph_noble@hotmail.com wrote:[color=blue]
> 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 (ralph_noble@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"));
>[/color]

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

re: Update Query (Access vs. SQL Server)

Trevor Best wrote:
[color=blue]
> 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')[/color]

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

re: Update Query (Access vs. SQL Server)

Trevor Best <nospam@besty.org.uk> wrote in news:41f9f700$0$26019
$fa0fcedb@news.zen.co.uk:
[color=blue]
> ralph_noble@hotmail.com wrote:[color=green]
>> 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 (ralph_noble@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"));
>>[/color]
>
> 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')[/color]

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.

re: Update Query (Access vs. SQL Server)

Lyle Fairfield wrote:[color=blue]
> Trevor Best <nospam@besty.org.uk> wrote in news:41f9f700$0$26019
> $fa0fcedb@news.zen.co.uk:
>
>[color=green]
>>ralph_noble@hotmail.com wrote:
>>[color=darkred]
>>>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 (ralph_noble@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"));
>>>[/color]
>>
>>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')[/color]
>
>
> 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.
>[/color]

I was unaware of that syntax.

--
This sig left intentionally blank

re: Update Query (Access vs. SQL Server)

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