Update field from one table with value of field in another table

[AkAlan via AccessMonster.com]

I'm trying to create an update query but can't seem to get the syntax correct.
I have two tables, I'll define them and keep things simple for the sake of
the post:

1st table is tblEquipment
Equipment ID (pk)
EquipmentDescription

2nd table is tblMeters
MeterID (pk)
EquipmentID (fk)
MeterType
MeterDescription

I want to update tblMeters.MeterDescription = tblEquipment.
EquipmentDecsription Where tblMeters.MeterType = "Hours Meter"

What I have so far is this
UPDATE dbo.Meters
SET MeterDescription = dbo.Equipment.Description
WHERE (dbo.Meters.MeterDescription = [Hour Meter])

I know there should be a select statement or join here I'm just at a loss.
Thanks for any help

 

[Gianluca Poggi]

Non ho capito . I'm Italian

 

[AkAlan via AccessMonster.com]

Got the answer from a sql forum site. it works

ALTER PROCEDURE dbo.StoredProcedure2
AS UPDATE tblMeters
SET MeterDescription = E.EquipmentDescription
FROM tblMeters AS M JOIN
tblEquipment AS E ON E.EquipmentID = M.EquipmentID
WHERE M.MeterDescription = 'Hour Meter';

I'm trying to create an update query but can't seem to get the syntax correct.
I have two tables, I'll define them and keep things simple for the sake of
the post:

1st table is tblEquipment
Equipment ID (pk)
EquipmentDescription

2nd table is tblMeters
MeterID (pk)
EquipmentID (fk)
MeterType
MeterDescription

I want to update tblMeters.MeterDescription = tblEquipment.
EquipmentDecsription Where tblMeters.MeterType = "Hours Meter"

What I have so far is this
UPDATE dbo.Meters
SET MeterDescription = dbo.Equipment.Description
WHERE (dbo.Meters.MeterDescription = [Hour Meter])

I know there should be a select statement or join here I'm just at a loss.
Thanks for any help

 

[Sylvain Lafontaine]

Did anyone in the sql forum site asked you why you would want to do that in
the first place? There is already a relationship between the tables
tblMeters and and tblEquipment and this "description" value is a string; so
there is some big de-normalisation in performing this operation.

The fact that you are performing this operation only on the rows defined by
M.MeterDescription = 'Hour Meter' could lead to the assumption that it's
possible that two rows from the tables tblMeters and tblEquipment will have
the same EquipmentID but not the same Description. This doesn't look very
kasher to me.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Got the answer from a sql forum site. it works

ALTER PROCEDURE dbo.StoredProcedure2
AS UPDATE tblMeters
SET MeterDescription = E.EquipmentDescription
FROM tblMeters AS M JOIN
tblEquipment AS E ON E.EquipmentID = M.EquipmentID
WHERE M.MeterDescription = 'Hour Meter';

I'm trying to create an update query but can't seem to get the syntax
correct.
I have two tables, I'll define them and keep things simple for the sake of
the post:

1st table is tblEquipment
Equipment ID (pk)
EquipmentDescription

2nd table is tblMeters
MeterID (pk)
EquipmentID (fk)
MeterType
MeterDescription

I want to update tblMeters.MeterDescription = tblEquipment.
EquipmentDecsription Where tblMeters.MeterType = "Hours Meter"

What I have so far is this
UPDATE dbo.Meters
SET MeterDescription = dbo.Equipment.Description
WHERE (dbo.Meters.MeterDescription = [Hour Meter])

I know there should be a select statement or join here I'm just at a loss.
Thanks for any help