Help in updating table

[kpamrutha]

Hi,

I have two tables:
TableName1 : Transportation
Fields : id,order_id, shipzip, shipstate
No. of records: 984750


TableName2 : Blanks
Fields : id,order_id, shipzip, shipstate
No. of records: 465780

Table Blanks is a subset of Table Transportation. Table Blanks has all shipzip as blanks and shipstate as NULL. I want to update Blanks table for shipzip and shipstate fields from table Transportation such that order_id from Blanks = order_id from Transportation AND shipstate from Transportation is not NULL.

I have tried couple of update staements, insert statements, conditional statements but nothing seems to work. I would appreciate any suggestion on how to proceed with this. I am a lot frustrated now. Please help.

Thank you,
Amrutha.

[Phill Pafford]

DB Type? MSSQL, MySQL, Oracle, etc... ?

[kpamrutha]

I am running SQL query on MS Access

[yssirhc]

Insert statements are only for adding a new record to a table. You already have your records so you need an update.


UPDATE Blanks as B, Transportation as T SET B.shipzip=T.shipzip, B.shipstate=T.shipstate WHERE B.order_id=T.order_id AND T.shipstate<>NULL

I don't know what you are setting B.shipzip and shipstate to, so that's just a guess. And I don't know if my syntax for the null part is exactly right, might need quotes or something depending on the database.

You may have been getting confused since it looks like it's updating 2 tables and not just the one, but you tell it what needs updating in the SET part.

Anyway, I think that's how you do it. why you'd have 2 tables with the exact same fields is beyond me though