[ic] SQL update problems

[interchange-users@interchange.redhat.com (Ron Phipps)]

> From: interchange-users-admin@interchange.redhat.com
[mailto:interchange-
> users-admin@interchange.redhat.com] On Behalf Of Scott Andreas
> 
> Hello list
> We're using Interchange as a catalog mail order clearing house and
contact
> management
> Which so far is work excellently
> 
> 
> I'm trying to update certain records based on where criteria. Here is
what
> I
> have
> 
> [query list=1
> 	table=catalogs
> 	st=db
> 	sql="UPDATE catalogs
> 		SET shipmode = 0, catdropdate = `$tag->time{%Y%M%d}`
> 		WHERE country = 'US' AND show_qty = '1' AND shipmode =
'1' AND
> completed
> != '1'
> 
> "]
> 
> In my error log...
> 
> /admin/update_contacts.html Bad SQL statement: Parse error near
> `$tag->time{%Y%M%d}`
> > 		WHERE country = 'US' AND show_qty = '1' AND mv_shipmode
= '1'
> AND
> completed != '1'
> 
> It appears that the problem is $tag... but how can I put an update
time
> stamp on the row?

Try this:

[comment] this is so dropdate can be reused in the select query's where
statement [/comment]

[seti dropdate][tag op=time]%Y%M%d[/tag][/set] 

[query list=1
 	table=catalogs
 	st=db
 	sql="UPDATE catalogs
 		SET shipmode = '0', catdropdate = '[scratch dropdate]'
 		WHERE country = 'US' AND show_qty = '1' AND shipmode =
'1' AND completed != '1'
"]


> Also how can I display the rows effected by the UPDATE

Try the following:

[query list=1
 	table=catalogs
 	st=db
 	sql="SELECT * FROM catalogs
 		SET shipmode = '0', catdropdate = '[tag
op=time]%Y%M%d[/tag]'
 		WHERE country = 'US' AND show_qty = '1' AND shipmode =
'0' AND catdropdate='[scratch dropdate]' AND completed != '1'
"]

This will show all rows that were updated on [scratch dropdate].  If you
only want the rows updated in the last UPDATE statement you will need to
modify the catdropdate to hold time as well and then it will show all
records updated at that time.

Good luck,
-Ron