Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Help with a couple of ...
Microsoft Dynamics RMS (Archived)

Help with a couple of basic SQL queries...

(0) ShareShare
ReportReport
Posted on by Chris Schlichting 205

I need to run a couple of sql queries to edit some data prior to putting my store online. 

For starters, I need to set picture file names for items that do not already have data in the field. starting with the command below, how would I add an argument to only set the picture name where it is currently blank?

UPDATE item SET picturename = itemlookupcode + '.jpg'

Secondly, I would like to set the default weight for items with no weight entered to .04, I am guessing the argument would be the similar.

Finally, Before I go live, I need to set the webitem category to '1' for the items that have a restock point higher than 1.

 

Any insight would be greatly appreciated.  Though I have some basic programming knowledge, I am new to sql. 

 

Chris

*This post is locked for comments

  • Chris Schlichting Profile Picture
    Chris Schlichting 205 on at
    Re: Help with a couple of basic SQL queries...

    Worked perfectly.  Thanks so much.  Saved a lot of mouse clicks!

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Help with a couple of basic SQL queries...

    update item set picturename=itemlookupcode+'.jpg' where picturename=''

    update item set subdescription3='BRAND NAME' where itemlookupcode like 'XXX%'

  • Chris Schlichting Profile Picture
    Chris Schlichting 205 on at
    Re: Help with a couple of basic SQL queries...

    Thanks! Awesome!

    what would I add to the picture statement to get it to only add the value for items with no picture already entered?

    update item set picturename=itemlookupcode+'.jpg' where picturename=

    One more query I thought of.   I use the brand field to (subdescription3) to set the manufacturer of my items.  I would like to run several querys to set the brand based on the contents of the ILC.  With my items the first three digits determine the manufacturer.  

    update item set subdescription3= BRAND NAME where itemlookupcode contains XXX

    What would be the proper syntax for that?  

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Help with a couple of basic SQL queries...

    update item set picturename=itemlookupcode+'.jpg'

    update item set weight=.04 where weight=0

    update item set webitem=1 where restocklevel>1

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans