Anyone know how to do an update query? 1

[bigfoot]

This is what I want to do:
I have 2 tables - EXSHOLOG & logexpo

I want to get all of the records in exsholog where 2 fields match logexpo, then update those records and put an X in column showx4.

Like this:
I want to update EXSHOLOG and SET EXSHOLOG.SHOWX4 = 'X'
where exsholog.custno = logexpo.custno
And exsholog.progcd = logexpo.progcd

Here is my query, and it works in MS Access but not in SQL Server.

UPDATE EXSHOLOG, LogExpo SET EXSHOLOG.SHOWX4 = 'X'
WHERE ((exsholog.custno=logexpo.custno And exsholog.progcd=logexpo.progcd))

Can someone please tell me how to fix this.


I suck doing SQL code.

 

[JamesLean]

UPDATE exsholog
SET showx4 = 'X'
FROM exsholog e JOIN logexpo l
  ON e.custno = l.custno AND e.progcd = l.progcd

--James

 

[bigfoot]

Thank you James! I messed with this all morning. Never thought of this.

It works, but why?

Where does the e and l come from? Are they ghost tables created on the fly?

 

[nread]

They are an alias....

 

[JamesLean]

As nread says, they are just aliases I have assigned to the two tables.

The reason your original query failed is because you can only have one table specified in the UPDATE clause. If you want to use two or more tables you have to use the FROM clause as per my example.

--James

 

[bigfoot]

So is it kinda like this:

UPDATE exsholog
SET showx4 = 'X'
FROM exsholog AS e JOIN logexpo AS l
ON e.custno = l.custno AND e.progcd = l.progcd

I added AS.

 

[JamesLean]

That's exactly what it is! The AS keyword is optional.

--James

 

[bigfoot]

Thanks to you both.

And thanks James for the query. I just tried it and it works great.