Multiple row updates

[prajac]

Hello all,

How do I achieve the following in SQL??

update pmwrkord
set pmwrkord.pmwostatus = 'In Progress' where pmwrkord.pmwrkord_code = '53022037'
and pmwrkord.pmwostatus = 'Terminated' where pmwrkord.pmwrkord_code = '53022037;

I have tried a few variations using case when expressions but I must be missing something.

Any help would be greatly appreciated.

Chandra

 

[jee]

I am having a hard time seeing just what you are trying to do. It looks like you are setting a status flag in the same row(s) to two different values (same where clause for each), which is, of course, not possible.

If there is a typo in your example and what you are wanting to do is set the value of the "status" column, based on the value of the "code" column, why not use a trigger, so that every time the code is changed the status is automatically changed as well?

 

[prajac]

Sorry, it is a typo. I have about 60 records to update. I want to set the value of pmwrkord.status ( to either "In Progress" or "Terminated".
I have been supllied a spreadsheet with these status values and I am trying to update the status codes according to the work order numbers (pmwrkord.pmwrkord_code). eg

This is what it looks like now.

pmwrkord_code Status
53022037 Online
53022036 Online
53022045 Online

After the update it should look like this
pmwrkord_code Status
53022037 In Progress
53022036 Terminated
53022045 Completed

 

[lewisp]

Try

[tt]UPDATE pmwrkord
SET pmwrkord.pmwostatus = Decode(pmwrkord.pmwrkord_code,
'53022037','In Progress',
'53022036','Terminated',
'53022045','Completed');[/tt]

 

[prajac]

This has updated the three records as required but set the remainder of the records in the table to null.

 

[stefanhei]

after a rollback use:
UPDATE pmwrkord
SET pmwrkord.pmwostatus =
Decode(pmwrkord.pmwrkord_code,
'53022037','In Progress',
'53022036','Terminated',
'53022045','Completed',
pmwrkord.pmwrkord_code);

Stefan

 

[prajac]

Thanks for that. I got it to work using the following

UPDATE pmwrkord
SET pmwrkord.pmwostatus =
Decode(pmwrkord.pmwrkord_code,
'53022037','In Progress',
'53022036','Terminated',
'53022045','Completed',
pmwrkord.pmwrkord_code,pmwrkord.pmwostatus);

However, it took a long time and went through and updated every record in the table. Is there a more efficient way and what if I wanted to update another column at the same time. For example I want to achieve the following in one statement

update pmwrkord set pmwostatus = "Cancelled", spvend_code =”0236” where pmwrkord = “53022046”;
update pmwrkord set pmwostatus = “Jobbing", spvend_code =”0440” where pmwrkord = “53022046”;
update pmwrkord set pmwostatus = "Terminated", spvend_code =”0456” where pmwrkord = “53022046”;
Can it be done using a CASE expression? I found an example on the net but I couldn't get the syntax correct and unfortunately I can't find the example again. Please help as I have a lot of these type of updates to do.

Chandra

 

[prajac]

Sorry typo again - should have read

update pmwrkord set pmwostatus = "Cancelled", spvend_code =”0236” where pmwrkord = “53022046”;
update pmwrkord set pmwostatus = “Jobbing", spvend_code =”0440” where pmwrkord = “53022047”;
update pmwrkord set pmwostatus = "Terminated", spvend_code =”0456” where pmwrkord = “53022048”;

 

[stefanhei]

Sorry, mixed up the columns in my tip: pmwrkord.pmwrkord_code should be pmwrkord.pmwostatus . The other column can be solved with a similar decode. But i'd overthink your DB-design. Wouldn't it make more sense to build a lookup-table. You could take the pmwrkord as key and have pmwostatus and spvend_code evaluated in your query - or a view, which represents the "human-readable" table.
Think of the space you'd save with this method:
Your way you have those strings (looks like aprox. 14 bytes) in EVERY column of your table. If your table is only 100000 rows you'd have 1400000 bytes (so aprox. 1,4MB) wasted. With the lookup-method you'd have all strings only stored ONCE in the DB, can use constraints to ensure integrity and have no need to update again when new records arrive.

Stefan

 

[sem]

Just add an appropriate where clause to limit the number of records being updated:

UPDATE pmwrkord
SET pmwrkord.pmwostatus = Decode(pmwrkord.pmwrkord_code,
'53022037','In Progress',
'53022036','Terminated',
'53022045','Completed')
where pmwrkord.pmwrkord_code in ('53022037','53022036','53022045')

Regards, Dima