471,108 Members | 1,296 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Arrrgh! UPDATE tbl1 from tbl2 - what am I doing wrong?

Need to update col dated in tbl po_history from col date_expense in tbl
po_expenses. Have tried, back ticks, single quotes, no quotes, etc.
left join and no join. No joy. Results is consistent Error Code 1064.
Searched MySQL.com it appears that my syntax and usage is correct, but
still get the parse error. Representative sample is below. Running a
select provides desired rows, attempting update throws error. MySQL is
v:3.23.33.

select po_history.rid, fk2_po_expense, dated, date_expense, `b`.rid from
`po_history`
left join `po_expenses` as b
on (`po_history`.fk2_po_expense = `b`.rid)
where fk2_po_expense >=1000

---- or ------

select po_history.rid ,fk2_po_expense ,dated, date_expense ,po_expenses.rid
from `po_history`, `po_expenses`
where fk2_po_expense >=1000 and (`po_history`.fk2_po_expense =
`po_expenses`.rid)

example results from either above: ==== (as expected) ========
rid fk2_po_expense dated date_expense po_expenses.rid
1022 1012 0000-00-00 2004-04-05 1012
1023 1113 0000-00-00 2004-04-06 1113
1052 1214 0000-00-00 2004-06-18 1214
1090 1310 0000-00-00 2004-04-09 1310
1141 1411 0000-00-00 2004-06-30 1411
1319 1508 0000-00-00 2004-04-20 1508
1320 1600 0000-00-00 2004-04-21 1600
1321 1720 0000-00-00 2004-04-21 1720
1322 1721 0000-00-00 2004-04-21 1721
1323 1723 0000-00-00 2004-04-21 1723

update `po_history`, `po_expenses`
set dated = date_expense
where fk2_po_expense >=1000 and (`po_history`.fk2_po_expense =
`po_expenses`.rid)
Error Code : 1064
You have an error in your SQL syntax near ' `po_expenses`
set dated = date_expense
where fk2_po_expense >=1000 and (`po_h' at line 1

from MySQL the 1064 translates to:
Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at
line %d

I'm lost ...

TIA

Please respond to the group
or remove _

Jul 20 '05 #1
0 1250

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Larry Jaques | last post: by
23 posts views Thread by JC | last post: by
20 posts views Thread by Terry | last post: by
2 posts views Thread by Jonas Cord | last post: by
2 posts views Thread by Leonardo | last post: by
6 posts views Thread by Tim Marshall | last post: by
1 post views Thread by musosdev | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.