What are the challenges you face when working across database platforms? Take the survey
Options

Using an alias with an update does not show suggestions

sbensonsbenson Posts: 17
edited August 18, 2009 10:51AM in SQL Prompt Previous Versions
In the example below, no suggestions are displayed for either alias. if I type tsip. I would expect to get sugestions. Instead, SQLPrompt does not display anything.

UPDATE tsip
SET tsip.SIVMDVendorID = as.Vendor_ID
FROM dbo.tblSIImportPosted tsip
JOIN dbo.Audit_Suppliers cas ON tsip.SIImportPostedID = as.StagingID

if I remove the update statement and type select with the alias, it shows the suggested columns.

Comments

  • Options
    Anu DAnu D Posts: 876 Silver 3
    Thanks for your post.

    I was trying to replicate the issue with reference to the query you have posted above.

    I found some issues with the alias you used dbo.Audit_Suppliers cas and the alias in Join condition as.StagingID do not match.

    Could you kindly elaborate more on this issue along with the query?
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • Options
    Thanks for your post.

    I was trying to replicate the issue with reference to the query you have posted above.

    I found some issues with the alias you used dbo.Audit_Suppliers cas and the alias in Join condition as.StagingID do not match.

    Could you kindly elaborate more on this issue along with the query?

    Sorry, changed a table name and thus the alias for the post. Basically, for any update query that invloves an alias, SQL Prompt is not providing suggestions. Another sample is provided below. Again, if I type the alias and the period after it, i would expect suggested columns to come up. nothing happens. This worked in version 3.x, so not sure if is a setting or a bug. My setting are pretty much default.

    UPDATE mi
    SET mi.input_item_records = ii.records,
    FROM #mi mi
    JOIN ( SELECT mid ,
    COUNT(*) AS records
    FROM #ii
    GROUP BY mid
    ) ii ON mi.MID = ii.MID
  • Options
    Anu DAnu D Posts: 876 Silver 3
    Thanks for your input.

    I have logged this in our internal tracking system whose id is SP-2730, so that it can be considered in our future releases.

    I will update you as soon as the bug is fixed.

    Many thnaks for your patience with this issue.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
Sign In or Register to comment.