Update query

[RosieGp]

I have the follwoing query that is updating the data in table SupplierTable1

UPDATE SupplierTable1 
SET    TodayDate = '#DATEFORMAT(Now(), "mm/dd/yyyy")#',
       Suppliermail = '#form.Supplieremail#',    
       SupplierStreet = '#form.SupplierStreet#',
       SupplierCity = '#form.SupplierCity#', 
       SupplierState = '#session.SupplierState#',    
       SupplierMiles = '#session.SupplierMiles#',
       SupplierPhone = '#session.SupplierPhone#',    
       SupplierMailBox = '#session.SupplierMailBox#'
WHERE SupplierID=#form.SupplierID#

Now I want the above query to update data only if SupplierID exists in VendorTable and the orderDate is not null.

I have another UPDATE query

UPDATE SupplierTable1 
SET    TodayDate = '#DATEFORMAT(Now(), "mm/dd/yyyy")#',
       Suppliermail = '#session.Supplieremail#',    
       SupplierStreet = '#session.SupplierStreet#',
       SupplierCity = '#session.SupplierCity#',
       SupplierState = '#form.SupplierState#',    
       SupplierMiles = '#form.SupplierMiles#',
       SupplierPhone = '#form.SupplierPhone#',    
       SupplierMailBox = '#form.SupplierMailBox#'
WHERE SupplierID=#form.SupplierID#

Now I want the above query to update data only if SupplierID exists in VendorTable and the orderDate is null.
CAN I COMBINE BOTH Queries.

 

[gmmastros]

Usually, this sort of thing is accomplished using an exists query, like this:

If Exists(Select 1 From Vendor where VendorId = '#Whatever#' orderDate is not null)
  Begin
    -- Your update query here
  End

You can also use 'Not Exists' like this:

If [!]Not[/!] Exists(Select 1 From Vendor where VendorId = '#Whatever#' orderDate is not null)
  Begin
    -- Your update query here
  End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

I see you are using the # symbol as a wildcard. Is this for Access? If so, I'd suggest posting in an Access forum on this site. This is for Microsoft SQL Server and while some of the SQL code is the same, there is enough different that anything we suggest for you might not work.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

It's ColdFusion. The OP is asking much of the same questions in the Coldfusion forum.

-----------
With business clients like mine, you'd be better off herding cats.

 

[SQLBill]

Thanks Philhege for letting me know that...I've never used ColdFusion so the #'s threw me off.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875